left, instr, trim??? update query

[kato68]

Im trying to create and update query that will extract the state out of an address field, the address field containing "city state" (no comma). I have been messing with the instr, left, etc.. functions and I cannot seem to get it right. The closest I have gotten is using :

Right(Trim([ADDR2]),Len(Trim([ADDR2]))-InStr(InStr(1,[ADDR2]," ")+1,[ADDR2]," "))

But this only works if my city is 2 words. For example
'chester springs pa' returns 'pa'
'but malvern pa' returns 'malvern pa'

Can someone help me get this query right?!?!?!

 

[MattS]

This isn't pretty, but works with the examples given:

Right(Trim([ADDR2]),Len(Trim([ADDR2]))-InStr(IIf(InStr(InStr([ADDR2]," ")+1,[ADDR2]," ")>0,InStr(InStr([ADDR2]," "),[ADDR2]," ")+1,1),[ADDR2]," "))

Hope this helps!

 

[kato68]

Thanks, that works great!

Any ideas on how to extract the city?
I haven't started messing around with that part yet, Im not sure how complicated it will be.

 

[MattS]

Try:

Left(Trim([ADDR2]),InStr(IIf(InStr(InStr([ADDR2]," ")+1,[ADDR2]," ")>0,InStr(InStr([ADDR2]," "),[ADDR2]," ")+1,1),[ADDR2]," ")-1)

 

[MattS]

Just a thought, but both of the above expressions will only work if the City is one or two words. Because it looks for a second space to use as the cut-off point, it will produce duff results for any string with more than two spaces in it, i.e.:

Salt Lake City UT would produce "Salt Lake" as the city, and "City UT" as the state.

If this is an issue you'll have to nest another IIF statement in there.

Hope this helps.

 

[kato68]

Good point. Ill get to that after I get what I have right now working correctly.

Thanks a bunch!