Parse and update Query 2

[ThunderGeek]

Greetings
Here is the problem:
I am using an update query to update a UPS shipping table,from my shipping database
I have a field called [Ship CSV] within this field is combines 1.the customers Name, 2. State, and 3. Zip code.
The format is as such:

OAK LAWN, IL 60453

The City is seperated by a comma, then one space to seperate state, and then two spaces to seperate zip.
I am able to pase out the Zip and City:
ParseZip: Right$([Ship CSZ],Len([Ship CSZ])-InStr(5,[Ship CSZ],",")-5)
ParseCity: Left$([Ship CSZ],InStr(1,[Ship CSZ],",")-1)

but I can not get the State to seperate.
Any help would be appericated.
Thanks TG

 

[Golom]

ParseState: Mid$([Ship CSZ],InStr(1,[Ship CSZ],",")+2,2)

 

[ThunderGeek]

Golom
Works Great!
Many Thanks
TG

 

[jaaret]

I'm getting some strange update query results when parsing a field in Access 2002. I'm parsing a property tax ID so it can successfully join with the tax ID field in a linked table. For example, an original value in the Kparcel field is: K0324059002

The code I'm using in the query is:

Parcel: Left(Right([kparcel],10),6) & " " & Right([kparcel],4)

On *most* tax id's it returns the correct result:
032405 9002

But on others it returns 59002 (and six spaces afterwards).

I can't see any pattern to why some results are correct and others are not. The format of the original value is identical for all records. The incorrect results show up in both select and update queries. Help!