split a column into 2 columns in access

Dscar

Hi, I am a beginner in ACCESS, I've imported data into access, and then realized that I need to split the information in one of my columns into 2 columns. the information looks like this:
00700400000000
00700400001230
00700400102000

I would like 007004 in one column and the rest of the info in another column. I know in excel I could do text to columns, however the is 20,000 records in access and can not export to excel and split the info. Do you have any tips (please be very basic, I am such a novice) thank you

ADezii

Quote:

Originally Posted by Dscar

Hi, I am a beginner in ACCESS, I've imported data into access, and then realized that I need to split the information in one of my columns into 2 columns. the information looks like this:
00700400000000
00700400001230
00700400102000

I would like 007004 in one column and the rest of the info in another column. I know in excel I could do text to columns, however the is 20,000 records in access and can not export to excel and split the info. Do you have any tips (please be very basic, I am such a novice) thank you

ASSUMPTIONS:

Table name is tblTest.
Column1 in tblTest contains the complete String. e.g. 00700400102000
Column2 is empty and will contain the leftmost 6 chararacters after the Update. e.g. 007004.
Column3 is empty and will contain the 8 rightmost characters after the Update. e.g. 00102000.
The Column1 Field always contains 16 characters.

Expand|Select|Wrap|Line Numbers

 Dim strSQL As String
 
DoCmd.SetWarnings False     'No Prompts, please

  strSQL = "UPDATE tblTest SET tblTest.Column2 = Left$([Column1],6), tblTest.Column3 = Right$([Column1],8);"

  DoCmd.RunSQL strSQL

DoCmd.SetWarnings True

Dscar

Thank you for responding. Were do I actually put this, Is it in a Query? I'm sorry, but I recently took a basic class, I'm not sure where to put the Code: (vb) you gave me. Also I see under 2. there is nothing. Can you help me understand how to accomplish this task?

Thank you

split a column into 2 columns in access

re: split a column into 2 columns in access

re: split a column into 2 columns in access

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about