Help!! Extracting data from a single field

[Duyen]

I have one field that contains City and State; but I really want two fields
(one for City and one for State)

For Example:
filed 1
Kansas City MO
Kansas City, KS

How can I extract these out into two columns
field 1 field 2
Kansas CIty MO
Kansas City KS


Please help!!!!

 

[Jerry Whittle]

Your example doesn't show a comma between the city and state in both records.
Is this correct? If there was a comma on every records, I have some code that
will do it.

On the other hand, it does show a 2-letter state. Is this true of every
record? If so you could use the Right function to extract the state.

State: Right([Field Name], 2)

Something like the below would extract the city; however it would leave
commas. You could use the Replace function to clean them up afterwards in a
secod query.

City: Left([Field Name], Len([Field Name]) -3)

 

[Duyen]

Thank you so much Jerry, that works out very well.
YOu have save me. Thanks again

Your example doesn't show a comma between the city and state in both records.
Is this correct? If there was a comma on every records, I have some code that
will do it.

On the other hand, it does show a 2-letter state. Is this true of every
record? If so you could use the Right function to extract the state.

State: Right([Field Name], 2)

Something like the below would extract the city; however it would leave
commas. You could use the Replace function to clean them up afterwards in a
secod query.

City: Left([Field Name], Len([Field Name]) -3)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have one field that contains City and State; but I really want two fields
(one for City and one for State)

For Example:
filed 1
Kansas City MO
Kansas City, KS

How can I extract these out into two columns
field 1 field 2
Kansas CIty MO
Kansas City KS


Please help!!!!