splitting a city-state field into two seperate fields

[Miss Bianca]

I have a city-state field in one of my tables. I need to
come up with a way to generate seperate city and state
fields. The state was easy...just take the last two
characters of the field. My struggle is getting the city.

I've tried this:
Left(tblcustomer!citystate,InStr(1,tblcustomer!
citystate," ")-1)

It worked great for cities that were all one word.
Unfortunately, at least a third of my cities are two
words.

All I need is the opposite of a concognate. (citystate
minus state) but I can't figure out how to make this
happen.

Any suggestions would be greatly appreciated.

Thanks!
Miss Bianca

 

[Steve Schapel]

Miss Bianca,

Would this work?...
Left([CityState],Len([CityState])-3)

- Steve Schapel, Microsoft Access MVP