Access expressions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to use the "Right" expression to take a piece of information from
one column of data in order to create a new column, but it's not working.
Any suggestions? I'm trying to insert state abbreviations that are already
listed in a CITY_STATE column into a new column. Also, anyone know how to
delete the state abbreviations from the other column?
 
Stacy - if you know how to use the Right expression to extract the state, you
can also use the left to extract the city, but may have to run it a few times
to account for one, two and three word city names. I don't have the exact
expression on hand, but feel free to email me the query attachment and I'll
correct it for you.

Destin Richter
(e-mail address removed)
 
I'm trying to use the "Right" expression to take a piece of information from
one column of data in order to create a new column, but it's not working.
Any suggestions? I'm trying to insert state abbreviations that are already
listed in a CITY_STATE column into a new column. Also, anyone know how to
delete the state abbreviations from the other column?
Click to expand...

Are the State abbreviations all in 2 character format, i.e. NY for New
York, CA for California?
And what separates the City name from the State?
A comma and space?

New York, NY

If so:
State:Right([CombinedField],2)

City:Left([CombinedField],Len([CombinedField])-5)
or
City:Left([CombinedField],InStr([Combinedfield],", ")-1)
 
Do you already have the new column defined in your table? If so, you might try

UPDATE YourTable
SET StateColumn = Right(City_State,2)
WHERE City_State Is Not Null

That assumes that the last two characters are the State.

I would add another column for CITY and do an update to it as follows.

UPDATE YourTable
SET CityColumn = Left(City_State,Len(City_State)-3)
WHERE City_State Is Not Null

That assumes your data in City_State is stored like

Omaha NE
New York City NY

IF it has a comma in it, then you will need to adjust the -3 to -4 to get
desired results.
Omaha, NE
 
Back
Top