A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

field manipulation



 
 
Thread Tools Display Modes
  #1  
Old May 28th, 2004, 02:29 PM
Steve
external usenet poster
 
Posts: n/a
Default field manipulation

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
  #2  
Old May 28th, 2004, 02:39 PM
JulieD
external usenet poster
 
Posts: n/a
Default field manipulation

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

"Steve" wrote in message
...
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



  #3  
Old May 28th, 2004, 03:12 PM
Cheryl Fischer
external usenet poster
 
Posts: n/a
Default field manipulation

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,
--

Cheryl Fischer, MVP Microsoft Access



"Steve" wrote in message
...
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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:39 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.