field manipulation

[Steve]

Hello,

I have a mailing list i created in access from an
external file. I am trying to figure out how to create a
new field from part of the data in another field. For
example: I have the follwing fields in my DB,
NAME1, ADDR1, ADDR2. Under the name field my records are
entered as lastname, first name. I want to create 2
fields from this field one with only first name and one
with only last name. Additionally in ADDR2 are the city
state zip data, I want to create a seperate field for
each pice of data in the ADDR2 field. The data in the
NAME1 field is seperated by coma and the data in the
ADDR2 field is seperated by a space. So my resulting
database would have a seperate field for NAME1 NAME2
ADDR1 CITY STATE ZIP. ANy help would be greatly
appreciated.

Thanks
Steve

 

[JulieD]

Hi Steve

if it was me, i would export the data out to excel, use the wonderful "text
to columns" feature found under the data menu & then reimport the data into
access and use an update query to put it all back together

failing that AFAIK you'll need to write some code to do what you want.

If the excel solution isn't the one for you, let us know and i'll see if i
can drag out some code to do what you want - or someone else might have a
better solution for you.

Regards
JulieD

 

[Cheryl Fischer]

Steve,

Splitting the Name field should be easy, as the comma denotes where the last
name ends and first name begins. You can create two new fields in your
table: FirstName and LastName and then do an Update query to populate them.

To populate the LastName field, fill in a column in an Update Query as
follows:

Field: row LastName
Update to: Left([NAME1], InStr([NAME1], ",") -1)

To populate the FirstName field, fill in a column in an Update Query as
follows:

Field: row FirstName
Update to: Mid([NAME1], InStr([NAME1], ",") +2)

Skipping 2 spaces from the comma (for FirstName) will eliminate the
space after the comma. If there is no space following the comma, use +1.

I like JulieD's solution for sending your ADDR2 field to Excel and then
splitting the field into columns. However, plitting your ADDR2 field is
problematical, as you are not using a comma between City and State.
Consider the following:

Houston TX 77001 will return 3 columns
Rapid City SD 57701 will return 4 columns
Rapid City South Dakota 57701 will return 5 columns

If you only have a few of the 4- and 5-column examples, you could easily fix
these before importing them back from Excel.

If all of your ADDR2 data is like the Houston example above and you would
like to do this in an Access Update Query, here are the expressions:

Field: row City
Update to: Left([ADDR2],InStr([ADDR2]," ")-1)

Field: row State
Update to: Mid([ADDR2],InStr([ADDR2]," ")+1, InStrRev([ADDR2],"
")-InStr([ADDR2]," ")-1)

Field: row Zip
Update to: Mid([ADDR2], InStrRev([ADDR2]," ")+1)

hth,