440,581 Members | 1,966 Online
+ 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
4 Replies

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

 P: 61 I think this could be simpler if you only use the mid function Expand|Select|Wrap|Line Numbers Dim x(1 to 12) as integer Dim y as String y = cStr(290501049125) for i = 1 to len(y)   x(i) = mid(y,i,1) next   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     x(i) = Cint(mid(y,i,1))   Mar 17 '16 #4

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