Need to remove ONLY trailing spaces from field. 1

[weigoldk]

I have a database that has many fields with trailing spaces. I can easily get rid of the spaces if when the field is like this: (dots=spaces)

Smith.....

(I find the location of the first space then I use the LEFT function to collect all characters up to the "space" location")

space: (InStr([comma],Lname," "))
Lname_no_space: Left([Lname],([space]-1))

But what about this?

North..Carolina.....

Thanks in advance

 

[Golom]

Check out the RTrim function.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[weigoldk]

Thanks Golom,

After re-reading my original post, I had simplified my situtation way too much.

What I actually have is (.=space)

City,.State.Zip(always 5digits)....
Detroit,.Michigan.49888............
Big City,.North.Carolina.22222.....

What I need to end up with is a field with just City,.State

My original fields is always 40 characters in length. There is always just one space between city and state and between state and zip. There are always enough spaces at the end of the field to make the total length 40 characters.

So, the rtrim function alone would not help me to pull the city and state out of the field. However, it did get me thinking about working from the RIGHT side instead of from the left side (So, again THANKS Golom).

I first used the rtrim function to get rid of the spaces at the end of the field. Then I calculated the length of the field using the LEN function. Next I used the left function to extract the left most characters (Len minus 6--zip code is always 5 plus the one space between state and zip).
TRIMCSZ: TRIM([CSZ])
CSZLen: Len([TRIMCSZ])
TrimCS: (Left([TRIMCSZ],([CSZLen]-6)))

If you are an Access guru, please don't laugh too hard. I am probably going from New York to Detroit via Miami , but I do eventually get to the proper destination.

 

[PHV]

You need only one calculated field:
TrimCS: Left([CSZ], InStrRev(RTrim([CSZ]),' ')-1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886