Trim spaces and other characters 1

[jw5107]

I have a table that I'm working with - and it needs to be cleaned up a little bit...
The field values look like this:
ADIRONDACK SARANAC LAKE NY
BATSFIJORD BATSFIJORD NORWAY
BELLINGHAM INTL BELLINGHAM WASH

What I want the query to do is this:
ADIRONDACK
BATSFIJORD
BELLINGHAM INTL

I need to get rid of the big gap in between the airport name and the city name & only show the airport name..
Don't know how to set the trim function syntax up...!!
Any examples or suggestions...??
Thanks in advance..!!
jw5107

 

[jrbarnett]

SELECT Left$ (Fieldname, Instr (Fieldname, " "))
FROM Table

will work for the first two, by using a search for three spaces you include the last value without the location information.

John

 

[rjoubert]

This update query finds the occurrence of 5 spaces and cuts off at that point. Hopefully, that will allow you to keep the "INTL" in your third example. You can adjust the amount of spaces as you see fit.

UPDATE Table SET Field1 = Left([Field1], InStr(1, [Field1], "     ", 1))
WHERE (((InStr(1, [Field1], "     ", 1)) > 0));

 

[jw5107]

jrbarnett,

That worked perfect..!!
Thank you very much..!!
Star for ya!!

jw5107