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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|