Prev: Action Query Slow and Hourglass Method
Next: Using Functions in Queries/Assigning Values to Non-numericalEntri
From: John Spencer on 12 Mar 2010 13:00
If you ALWAYS have City Name space and State code then it is simple

StateCode: Right([Your Field],2)
CityName: Trim(Left([Your Field],Len([YourField])-2))

Using InstrRev
CityName: Left("Las Vegas NV",InStrRev("Las Vegas NV"," ")-1)
StateCode: Mid("Las Vegas NV",InStrRev("Las Vegas NV"," ")+1)

It gets a little more complex if you have fields that are blank or have just a
city name or have no spaces.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

jim wrote:
> I have a field that has both city and state. The state is abbrievated with 2
> characters. Of course, I am trying to seperate the two, so I need one field
> of my query to find the space and return the text left of the space and the
> next field of the query to find the text to the right. If tried using the
> InStrRev function, but can't seem to get what is needed. Following is a copy
> of the statement I am using. Any help will be apprecaited.
>
> Left([ADDR3],InStrRev([ADDR3]," ",-1,1))
 | 
Pages: 1
Prev: Action Query Slow and Hourglass Method
Next: Using Functions in Queries/Assigning Values to Non-numericalEntri