Removing Characters From a Field

[SMatthews]

I have a field that contains a city and a state (ie. New Orelans, LA). I would like to find a way to strip the state out of the field, put it in another field, and leave the city in the original field. Is there an easy way to do this? Thanks in advance.

 

[boblarson]

Need a little more info. Are you trying to change a table, display it on a form, or display in a report?

Your answer will define the answer to the problem.

BL

 

[raskew]

OK, say you've got tblAddressFix with fields:

AddressID - Autonumber (key)
MyAddress - Text
MyState - Text

In this example, It's populated with two records--field [MyAddress] contains (1) "New Orleans, LA" and (2) "Los Angeles, CA"

The intent is to place the state abbreviation in field [MyState], and remove it from field [MyAddress].

Use an Update query. The InStr() function will allow you to determine the position of the delimiting comma-left of the comma is the city, right of the comma is the state, i.e.:

UPDATE tblAddressFix SET tblAddressFix.MyAddress = Left([MyAddress],InStr([MyAddress],",")-1), tblAddressFix.MyState = Trim(Mid([MyAddress],InStr([MyAddress],",")+1));


This can all be done from the query grid, starting with a Select query, then transforming it into an Update query and adding the code to the Update To blocks.

 

[SMatthews]

Thanks Raskew! That worked well for the records that had commas in it. I did have quite a few records with no commas though (you gotta love those data entry people). I figured out how to extract the city from those records though. Thanks again!