split string

[Mark]

Hi,
I use Access2k. My question is:
How to split a string. One field contains
city, state and zip, like:

LOS ANGELES CA 90032
WINSTON SALEM NC 27104
REDWOOD CITY CA 94063

I want to write a query to split it to 3 fields, like:
field1 field2 field3
LOS ANGELES CA 90032
WINSTON SALEM NC 27104
REDWOOD CITY CA 94063

How to write these query?
Thanks,

 

[fredg]

Hi,
I use Access2k. My question is:
How to split a string. One field contains
city, state and zip, like:

LOS ANGELES CA 90032
WINSTON SALEM NC 27104
REDWOOD CITY CA 94063

I want to write a query to split it to 3 fields, like:
field1 field2 field3
LOS ANGELES CA 90032
WINSTON SALEM NC 27104
REDWOOD CITY CA 94063

How to write these query?
Thanks,

Assuming ALL the data is as indicated above,
5 digit Zip code, 2 character State, all separated by a space, and you
are using Access 2000 or newer.

City: Left([CombinedNames],InStrRev([CombinedNames]," ",
Len([CombinedNames])-7))

State: Mid([CombinedNames],InStrRev([CombinedNames]," ",
Len([CombinedNames])-7)+1,2)

Zip: Mid([CombinedNames],InStrRev([CombinedNames]," ")+1)