By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,871 Members | 2,418 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,871 IT Pros & Developers. It's quick & easy.

split a column into 2 columns in access

P: 2
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
May 16 '07 #1
Share this Question
Share on Google+
2 Replies


ADezii
Expert 5K+
P: 8,628
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:
  1. Table name is tblTest.
  2. Column1 in tblTest contains the complete String. e.g. 00700400102000
  3. Column2 is empty and will contain the leftmost 6 chararacters after the Update. e.g. 007004.
  4. Column3 is empty and will contain the 8 rightmost characters after the Update. e.g. 00102000.
  5. The Column1 Field always contains 16 characters.
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. DoCmd.SetWarnings False     'No Prompts, please
  4.   strSQL = "UPDATE tblTest SET tblTest.Column2 = Left$([Column1],6), tblTest.Column3 = Right$([Column1],8);"
  5.   DoCmd.RunSQL strSQL
  6. DoCmd.SetWarnings True
May 16 '07 #2

P: 2
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
May 17 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.