Trim Space from the LEFT side of the field

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I have a field in my table that contains a 5 Digit Zip code and a city name,
but there is no identifier as to where the city name starts because the zip
code doesn't always start from the beginning, it could have 1 or 2 spaces,
so it may be 7 digits after the field starts, Now I want to run an update
query that should update the field to remove any spaces from the LEFT side
of the field, for example:

10018 New York

Should become

10018 New York

And then I will be able to run a query that will remove the first 5 digits
and place it in another field, so that I will have a Zip Code Field and a
city field
 
Scott said:
I have a field in my table that contains a 5 Digit Zip code and a city name,
but there is no identifier as to where the city name starts because the zip
code doesn't always start from the beginning, it could have 1 or 2 spaces,
so it may be 7 digits after the field starts, Now I want to run an update
query that should update the field to remove any spaces from the LEFT side
of the field, for example:

10018 New York

Should become

10018 New York

And then I will be able to run a query that will remove the first 5 digits
and place it in another field, so that I will have a Zip Code Field and a
city field
Click to expand...

Ltrim will work but so will Trim since you don't care what's after the city.
I'd suggest using InStr to find the space unless you are positive this never
has any ZIP+4 info.

And if anybody from MSFT is reading the Pick Trim function is *much* better
than yours (and about the same age)
It reduces all internal spaces to a single space.
"this becomes"
"this becomes"
 
Back
Top