I have a column with city and state in one field (separated by a space and/or 
a comma). I want to separate the information into two fields (similar to what 
you can do in excel using the "Text to Columns" function). How can I do this 
using queries?

(separated by a space and/or a comma). 

Here's the sticking point. If every field has a comma, you could use 
combinations of the InStr, Left, and Mid functions to find the commas then 
split up the field there. Therefore you need to make sure that every record 
has a comma.

How about the space? Two words: St. Louis. Two more: San Diego.

Depending on the space could give you really bad results.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"tapasaddict" wrote:

> I have a column with city and state in one field (separated by a space and/or 
> a comma). I want to separate the information into two fields (similar to what 
> you can do in excel using the "Text to Columns" function). How can I do this 
> using queries?

On Tue, 26 Jan 2010 10:11:01 -0800, tapasaddict
<tapasaddict@discussions.microsoft.com> wrote:

>I have a column with city and state in one field (separated by a space and/or 
>a comma). I want to separate the information into two fields (similar to what 
>you can do in excel using the "Text to Columns" function). How can I do this 
>using queries?


Des Moines Iowa
Grand Forks Minnesota
Salt Lake City Utah
West Fargo North Dakota


In other words... insufficient information exists in the text string for any
algorithm to do this unambiguously. You'll need either a table of states (with
all the possibilities the data might contain, such as MN, Minn, ND, NDak,
etc.) or a USB (Using Someone's Brain) interface.

You can get a first pass by filtering out the names with a comma:

City: Left([CityAndState], InStr([CityAndState], ",") - 1)
State: Mid([CityAndState], InStr([CityAndState], ",") + 1)

using a criterion of LIKE "*,*" to select only records containing  a comma;
and

City: Left([CityAndState], InStrRev([CityAndState], " ") - 1)
State: Mid([CityAndState], InStrRev([CityAndState], " ") + 1)

with a criterion of 

NOT LIKE "*,*"

The latter set, at least, will need manual processing for "West Fargo North"
in the state of "Dakota", every city in West Virginia, and so on.
-- 

             John W. Vinson [MVP]