is there any way to split data from one field into three fields

[Guest]

I have a table with a field that contains city, state and zip data. Each of
these items are separated by one space character. I want to split the data
in this field to three fields one for city, one for state, and one for the
zip code. I was looking at at the left and right string functions but I
don't know how the specify the number of characters because the different
cities are not the same length. I want to use the occurance of a space the
determine the number of characters in the field to use. Is this the way to
go and what is the syntax of the command?

 

[John Vinson]

I have a table with a field that contains city, state and zip data. Each of
these items are separated by one space character. I want to split the data
in this field to three fields one for city, one for state, and one for the
zip code. I was looking at at the left and right string functions but I
don't know how the specify the number of characters because the different
cities are not the same length. I want to use the occurance of a space the
determine the number of characters in the field to use. Is this the way to
go and what is the syntax of the command?

Yes: InStr() is a function which finds the position of a blank. I'd
suggest updating City to

Left([CityStateZip], InStr([CityStateZip], " ") - 1)

State to

Left(Mid([CityStateZip], InStr([CityStateZip], " ") + 1),
InStr(Mid([CityStateZip], InStr([CityStateZip], " ") + 1), " ") - 1)

Zip to

Right([CityStateZip], 5)

assuming only five digit zips.

Watch out for "Salt Lake City", "Los Angeles", and other such cities
with blanks in their names. They'll mess this logic seriously, and
will probably require special handling. Are your zips always there?
Are they always five characters? Are the states always there, and are
they consistantly either state names or two-letter codes? There might
be a lot of special handling required!

John W. Vinson[MVP]

 

[Guest]

I know there are no two word cities and the state is always 2 characters but
the zip is either the 5 or 9 digit code.

I have a table with a field that contains city, state and zip data. Each of
these items are separated by one space character. I want to split the data
in this field to three fields one for city, one for state, and one for the
zip code. I was looking at at the left and right string functions but I
don't know how the specify the number of characters because the different
cities are not the same length. I want to use the occurance of a space the
determine the number of characters in the field to use. Is this the way to
go and what is the syntax of the command?

Yes: InStr() is a function which finds the position of a blank. I'd
suggest updating City to

Left([CityStateZip], InStr([CityStateZip], " ") - 1)

State to

Left(Mid([CityStateZip], InStr([CityStateZip], " ") + 1),
InStr(Mid([CityStateZip], InStr([CityStateZip], " ") + 1), " ") - 1)

Zip to

Right([CityStateZip], 5)

assuming only five digit zips.

Watch out for "Salt Lake City", "Los Angeles", and other such cities
with blanks in their names. They'll mess this logic seriously, and
will probably require special handling. Are your zips always there?
Are they always five characters? Are the states always there, and are
they consistantly either state names or two-letter codes? There might
be a lot of special handling required!

John W. Vinson[MVP]

 

[John Vinson]

I know there are no two word cities and the state is always 2 characters but
the zip is either the 5 or 9 digit code.

Then you'll need to adapt the InStr() expression I used for State to
find the second blank - or, update CityStateZip to

Mid([CityStateZip], InStr([CityStateZip], " ") + 1)

in the same update query as used for City to pare it down to just
state and zip, thereby getting a simpler query for the second round.

John W. Vinson[MVP]