splitting data in a query

[guidop12]

I have a field in a table that is formatted as such City, State ZIp
(ex:Utica, NY 13501). I would like to split first the City and have it
updatted to the city fieldin the same table, then the state, then the zip.
I first tried splitting the city with the following format:
City:left(address3,InStr(1,Address3, ",")+1)
This serparted the city but didn't update the city field.
Any help would be appreciated.

 

[John Spencer]

If you want to actually put the value into the City field, you need to use an
UPDATE query.

-- Open your query in design view
-- Select Query: Update from the menu
-- Enter the expression in the Update To "cell" under the City field
Left([address3],InStr(1,[Address3], ",")-1)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[guidop12]

Thanks John, they did work for the city, but I'm running into another
problem. I have hardcoded the state to be NY because they are all NY. Now I
need to extract the zip part which in the field it is inputted as NY
13501(two spaces after the state), I tried to experiment with RIGHT but keep
getting errors.

Thanks again

If you want to actually put the value into the City field, you need to use an
UPDATE query.

-- Open your query in design view
-- Select Query: Update from the menu
-- Enter the expression in the Update To "cell" under the City field
Left([address3],InStr(1,[Address3], ",")-1)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have a field in a table that is formatted as such City, State ZIp
(ex:Utica, NY 13501). I would like to split first the City and have it
updatted to the city fieldin the same table, then the state, then the zip.
I first tried splitting the city with the following format:
City:left(address3,InStr(1,Address3, ",")+1)
This serparted the city but didn't update the city field.
Any help would be appreciated.

 

[John Spencer]

Use an update query that looks like the following
Field: Zip
Update To: Right([Address3],5)
Criteria: Like "* #####"

That should only populate the zip code field when the last six characters in
the field consist of a space followed by 5 numbers.





John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thanks John, they did work for the city, but I'm running into another
problem. I have hardcoded the state to be NY because they are all NY. Now I
need to extract the zip part which in the field it is inputted as NY
13501(two spaces after the state), I tried to experiment with RIGHT but keep
getting errors.

Thanks again

If you want to actually put the value into the City field, you need to use an
UPDATE query.

-- Open your query in design view
-- Select Query: Update from the menu
-- Enter the expression in the Update To "cell" under the City field
Left([address3],InStr(1,[Address3], ",")-1)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have a field in a table that is formatted as such City, State ZIp
(ex:Utica, NY 13501). I would like to split first the City and have it
updatted to the city fieldin the same table, then the state, then the zip.
I first tried splitting the city with the following format:
City:left(address3,InStr(1,Address3, ",")+1)
This serparted the city but didn't update the city field.
Any help would be appreciated.