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
|
|||
|
|||
Follow up Question – Convert one field into three fields
I did not know how to convert a one field address in EXCEL to a three field
address in ACCESS and I posted a question. The address in EXCEL was one field and I needed to convert the one field into three fields in ACCESS. For example, the EXCEL one field address was: 123 W Maple (one field). I needed to convert this to three fields in ACCESS. Field 1: 123; Field 2: W; Field 3: Maple. I received a reply to my question and I followed the instructions and everything worked. Except I have only two fields. Field 1: 123; Field 2: W Maple. Now I have a follow up question. After I ran the update query, the updated ACCESS address was in two fields. Field one was 123, and Field 2 was W Maple. Do you know how to get the street direction into a separate field? For example, the address is shown as 123 W Maple (all one field). I need to convert this to three fields in ACCESS. Field 1 is 123; Field 2 is W; and Field 3 is Maple. I used the method that was recommended and I imported the EXCEL worksheet into ACCESS. And I created the Update query and it worked. But I do not know how to get the street direction (N,S,E,W) in a separate ACCESS field. Do you know how I can create three fields in ACCESS from one field? This is what I used to create two fields from one field: Run an Update query updating AddressNumber to Left([ADDRESS], InStr([ADDRESS], " ") - 1) and Street to Mid([ADDRESS], InStr([ADDRESS], " ") + 1) Thank you for your help with this. Doug |
#2
|
|||
|
|||
Hi Doug,
You could always run the same update query, except just change the field names. For example, if the value "W Maple" is now saved in a field named "Street", then your update queries would become: Left([STREET], InStr([STREET], " ") - 1) and Street to Mid([STREET], InStr([STREET], " ") + 1) Personally, I find it much easier to split the data in Excel, and then import it. Open your spreadsheet and select the field that contains the full address. Then click on Data Text to columns... Choose the delimited option, and specify a space on the next screen. You can also place a tick in the "Treat consecutive delimiters as one" in case some of your data includes multiple spaces in-between parts of the address. Tom _____________________________________________ "Doug" wrote: I did not know how to convert a one field address in EXCEL to a three field address in ACCESS and I posted a question. The address in EXCEL was one field and I needed to convert the one field into three fields in ACCESS. For example, the EXCEL one field address was: 123 W Maple (one field). I needed to convert this to three fields in ACCESS. Field 1: 123; Field 2: W; Field 3: Maple. I received a reply to my question and I followed the instructions and everything worked. Except I have only two fields. Field 1: 123; Field 2: W Maple. Now I have a follow up question. After I ran the update query, the updated ACCESS address was in two fields. Field one was 123, and Field 2 was W Maple. Do you know how to get the street direction into a separate field? For example, the address is shown as 123 W Maple (all one field). I need to convert this to three fields in ACCESS. Field 1 is 123; Field 2 is W; and Field 3 is Maple. I used the method that was recommended and I imported the EXCEL worksheet into ACCESS. And I created the Update query and it worked. But I do not know how to get the street direction (N,S,E,W) in a separate ACCESS field. Do you know how I can create three fields in ACCESS from one field? This is what I used to create two fields from one field: Run an Update query updating AddressNumber to Left([ADDRESS], InStr([ADDRESS], " ") - 1) and Street to Mid([ADDRESS], InStr([ADDRESS], " ") + 1) Thank you for your help with this. Doug |
#3
|
|||
|
|||
PS. Insert at least three new empty columns to the right of the column in
your spreadsheet that you want to split. And, it should go without saying, but practice only on a copy of your spreadsheet (not the original). Tom _____________________________________________ "Tom Wickerath" wrote: Hi Doug, You could always run the same update query, except just change the field names. For example, if the value "W Maple" is now saved in a field named "Street", then your update queries would become: Left([STREET], InStr([STREET], " ") - 1) and Street to Mid([STREET], InStr([STREET], " ") + 1) Personally, I find it much easier to split the data in Excel, and then import it. Open your spreadsheet and select the field that contains the full address. Then click on Data Text to columns... Choose the delimited option, and specify a space on the next screen. You can also place a tick in the "Treat consecutive delimiters as one" in case some of your data includes multiple spaces in-between parts of the address. Tom _____________________________________________ "Doug" wrote: I did not know how to convert a one field address in EXCEL to a three field address in ACCESS and I posted a question. The address in EXCEL was one field and I needed to convert the one field into three fields in ACCESS. For example, the EXCEL one field address was: 123 W Maple (one field). I needed to convert this to three fields in ACCESS. Field 1: 123; Field 2: W; Field 3: Maple. I received a reply to my question and I followed the instructions and everything worked. Except I have only two fields. Field 1: 123; Field 2: W Maple. Now I have a follow up question. After I ran the update query, the updated ACCESS address was in two fields. Field one was 123, and Field 2 was W Maple. Do you know how to get the street direction into a separate field? For example, the address is shown as 123 W Maple (all one field). I need to convert this to three fields in ACCESS. Field 1 is 123; Field 2 is W; and Field 3 is Maple. I used the method that was recommended and I imported the EXCEL worksheet into ACCESS. And I created the Update query and it worked. But I do not know how to get the street direction (N,S,E,W) in a separate ACCESS field. Do you know how I can create three fields in ACCESS from one field? This is what I used to create two fields from one field: Run an Update query updating AddressNumber to Left([ADDRESS], InStr([ADDRESS], " ") - 1) and Street to Mid([ADDRESS], InStr([ADDRESS], " ") + 1) Thank you for your help with this. Doug |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
I need to convert a number field to autonumber | jglazier | Database Design | 2 | March 28th, 2005 03:31 PM |
convert text field to date | Doug | Setting Up & Running Reports | 5 | December 24th, 2004 12:40 AM |
convert text field to date field | captmatt | General Discussion | 1 | December 10th, 2004 10:46 PM |
question on a field type | Edwin Merced | General Discussion | 2 | October 13th, 2004 03:17 PM |
NUMBERING the pages | Bob | New Users | 7 | June 14th, 2004 12:20 AM |