Take data in a single column of access and split it into two colum

A

Accesschallenged

I have inheirited a database that has several hundred records that have the
city and state in the same column while the rest of the database has the city
in one column and the state in another. Is there a way to make all records
have the city in one column and the state in one column without having to
manually re-type it?
 
S

SirPoonga

This may not be easy. There's a way to get you close to what you want?

How are the city and state stored?
State abbreviated?
Comma between city and state?

Depending on how they are stored depends on how you are going to parse
the field.
 
S

SirPoonga

I hit send too soon.

What you are going to do, depending on how the data is formatted is
make your own parse funciton in a module. Have that function either
split (delimited by space, comma, etc..) to using the right() and
left() functions. The function will return the wanted part. Again,
need to know how the field is formattted first in order to figure that
out.
 
A

Accesschallenged

Nashville,TN is the format of the columns I want to change. Will this
require exporting the data?
 
D

Douglas J. Steele

City = Left$(CityAndState, InStr(CityAndState, ",") - 1)
State = Trim$(Mid$(CityAndState, InStr(CityAndState, ",") + 1))
 
S

SirPoonga

Ok, this should be easy then.

I am going to assume the field they are in is called CITYSTATE. Create
a CITY and a STATE field for that table. And I am going to assume they
are text (they should be :) And the state is the two letter
abbreviation following a comma and a space.

Create an update query based on that table. in the query builder.
Drag the CITY and STATE fields to the grid.

For the UPDATE to value in the CITY field type:
Left([CITYSTATE],len([CITYSTATE]) - 4)
That will take the left most characters of [CITYSTATE] minus the last 4
which should be ", TN"

For the UPDATE to value in the STATE field type:
Right([CITYSTATE],2)
Like the left function, right take the right most characters, in this
case the last two character of the string which should be the state.

Replace names as needed. backup before you run the query.
 
A

Accesschallenged

"Nashville,TN" is the format of the columns I want to change. Will this
require exporting the data?
 
A

Accesschallenged

Thanks. I will give this a try. :)

Douglas J. Steele said:
City = Left$(CityAndState, InStr(CityAndState, ",") - 1)
State = Trim$(Mid$(CityAndState, InStr(CityAndState, ",") + 1))
Click to expand...
 
A

Accesschallenged

Thanks. I will give this a try. :)

Accesschallenged said:
Nashville,TN is the format of the columns I want to change. Will this
require exporting the data?
Click to expand...
 
A

AndrewT.

Very similar to the problem you just solved, I also pose a question. I have
a database with 146,000 records. I have a field named Zip_10 (text). It
contains zipcodes with and without zip+4. the data is entered as follows:

48006
48123-0021
48323-0989
48123
48945-2321
All the records with a zip4 have a hypen. I need to make one column zip
(first 5 digits) and one column zip4 with the digits past the hypen.
I have created a new column called Zip4 but it is empty. I am below NOVICE
level on this stuff. I can do it in excel by using the text to column
command but my full database will not load. I truncates at 65,000 records or
so.
Any help?
 
Top