Update query for removing spaces

[Mike]

I need an example of an update query to remove leading spaces in the values
of a text field, e.g.: the value "_ABCD" should become "ABCD".
Thank you in advance for your tips!

 

[Marshall Barton]

I need an example of an update query to remove leading spaces in the values
of a text field, e.g.: the value "_ABCD" should become "ABCD".

UPDATE thetable
SET textfield = LTrim(textfield)

 

[Ember]

Could anyone provide a bit more instructions on how to use this solution. I
seem to be at a loss as to where this code should be typed.

I also have a field [File Name] in which some records have a leading space
that I would like deleted. Not all of my records have this space issue.

Please help, I am not interested in hand deleting 6900 records!

Thanks!

 

[Gary Walter]

Could anyone provide a bit more instructions on how to use this solution.
I
seem to be at a loss as to where this code should be typed.

I also have a field [File Name] in which some records have a leading space
that I would like deleted. Not all of my records have this space issue.

Please help, I am not interested in hand deleting 6900 records!

Thanks!

UPDATE thetable
SET textfield = LTrim(textfield)

In Database Window that shows "Objects"
in left pane, click on "Queries."

Then in right pane, click on "Create Query in Design View"

In the Show Table dialog box,
click on your table,
click Add,
and then click Close.

You should now show your table
in the query designer.

Double-click on the field in the table that you want to update.
It should appear in a column in the grid, something like:

{if your table were named "thetable"
and your field were named "textfield"}

Field: textfield
Table: thetable
Sort:
Show: <checked>
Criteria:
Or:

In upper menu click on "Query...Update Query"

Your grid should change to something like:

Field: textfield
Table: thetable
Update To:
Criteria:
Or:

In the "Update To:" row, type in something like:

LTrim([textfield])

where we wrapped the fieldname in brackets
then applied the trim function to that field.

the grid should now look like:

Field: textfield
Table: thetable
Update To: LTrim([textfield])
Criteria:
Or:

Save the query (say as "qryupdTrimField")

There are several ways to execute this update query.

While you have Query Designer open, click on the
red exclamation icon to run the update query.

What Marshall gave you was the SQL text for your query.

While in Query Designer, you can choose to go to
"SQL View" where you will see the "text" like Marshall
provided.

 

[Ember]

Thank you Gary...I had tried different "versions" of what both you and
Marshall had posted, however my "grammar" was not correct.

This worked perfectly and a great relief since the number of records to
update changed from 6900 to 11000! (New data was added after my inital
posting.)

Thank you again!
Ember

:

In Database Window that shows "Objects" in left pane, click on "Queries."

Then in right pane, click on "Create Query in Design View"

In the Show Table dialog box, click on your table, click Add, and then click
Close.

You should now show your table in the query designer.

Double-click on the field in the table that you want to update. It should
appear in a column in the grid, something like:

{if your table were named "thetable" and your field were named "textfield"}

Field: textfield
Table: thetable
Sort:
Show: <checked>
Criteria:
Or:

In upper menu click on "Query...Update Query"

Your grid should change to something like:

Field: textfield
Table: thetable
Update To:
Criteria:
Or:

In the "Update To:" row, type in something like:

LTrim([textfield])

where we wrapped the fieldname in brackets then applied the trim function to
that field.

the grid should now look like:

Field: textfield
Table: thetable
Update To: LTrim([textfield])
Criteria:
Or:

Save the query (say as "qryupdTrimField")

There are several ways to execute this update query.

While you have Query Designer open, click on the red exclamation icon to run
the update query.

What Marshall gave you was the SQL text for your query.

While in Query Designer, you can choose to go to "SQL View" where you will
see the "text" like Marshall provided.

UPDATE thetable
SET textfield = LTrim(textfield)