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

Need to Split 12-Digit Number Field into 12 Separate Fields

P: 1
EXp 290501049125 like this number field i need this to be split 12 fileds like Field1 (2) Field2 (9) filed3 (0) filed4 (5) etc. Can Any one help to solve this issue?
Mar 17 '16 #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 1,221
Expand|Select|Wrap|Line Numbers
  1. dim x(1 to 12) as string
  2. dim y as string
  3. dim intN1 as int
  4.  
  5. y = "290501049125"
  6. for intN1 = 1 to 11
  7.     x(intN1)=left(y,1) ' take 1 care of Y from the left
  8.     y = mid(y,2)   ' eliminate 1 char at a time from original string
  9. next
  10. x(12)= y
  11.  
Or if you prefer to do it with numbers
Expand|Select|Wrap|Line Numbers
  1. dim x(1 to 12) as integer
  2. dim y as long
  3. dim intN1 as integer
  4.  
  5. y = 290501049125
  6. for intN1 = 1 to 11
  7.     x(intN1)=y-((y/10)*10)
  8.     y=y/10
  9. next
  10. x(12)=y
  11.  
Mar 17 '16 #2

P: 61
I think this could be simpler if you only use the mid function

Expand|Select|Wrap|Line Numbers
  1. Dim x(1 to 12) as integer
  2. Dim y as String
  3. y = cStr(290501049125)
  4. for i = 1 to len(y)
  5.   x(i) = mid(y,i,1)
  6. next
  7.  
Mar 17 '16 #3

Expert 100+
P: 1,221
Yes, Rodney, nicely done. And then it may be advisable to wrap that mid function in a Cint(), thusly
Expand|Select|Wrap|Line Numbers
  1.     x(i) = Cint(mid(y,i,1))
  2.  
Mar 17 '16 #4

ADezii
Expert 5K+
P: 8,668
You can also populate a Table (tblSplit) with the results at the same time:
Expand|Select|Wrap|Line Numbers
  1. Dim y As String
  2. Dim MyDB As DAO.Database
  3. Dim rst As DAO.Recordset
  4. Dim intCtr As Integer
  5.  
  6. Set MyDB = CurrentDb
  7. Set rst = MyDB.OpenRecordset("tblSplit", dbOpenDynaset, dbAppendOnly)
  8.  
  9. y = "290501049125"
  10.  
  11. With rst
  12.   .AddNew
  13.     For intCtr = 1 To 12
  14.       .Fields(intCtr - 1) = Mid$(y, intCtr, 1)
  15.     Next
  16.   .Update
  17. End With
  18.  
  19. rst.Close
  20. Set rst = Nothing
Mar 18 '16 #5

Post your reply

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