How to perform an opposite of CONCATENATE on a form

  • Thread starter Thread starter matejp
  • Start date Start date
M

matejp

Hi,

I have a text field in format 000123456789 and I have to split this
text field into 12 separate text fields. So this would have to look
something like this:

Field1 has a value 0,
Field2 has a value 0,
Field3 has a value 0,
Field4 has a value 1,
Field5 has a value 2,
Field6 has a value 3,
Field7 has a value 4,
Field8 has a value 5,
Field9 has a value 6,
Field10 has a value 7,
Field11 has a value 8,
Field12 has a value 9.

I have to admit that I don't have a slightest clue on how to achieve
this and would really appreciate help. The reason I have to separate
this field is because I have to multiply each number inside the field
with a different ponder in order to calculate a control number for my
reports.

Thanks for your help,
Matej
 
I am assuming you have a table with a number of fields to hold each
individual number. The table should have enough fields to store each
individual number.

You can play around with this code if you like. You pass it your number or
code and it assigns the number to each field of the mySplitTable table
sequentially .



Public Function numSplitter(stg As String)
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM mySplitTable", CurrentProject.Connection,
adOpenDynamic

Dim counter As Integer
counter = 0

rst.AddNew
Do Until counter = Len(stg)
On Error GoTo errorme
counter = counter + 1
Debug.Print Mid(stg, counter, 1)
rst.Fields(counter) = Mid(stg, counter, 1)
Loop

exitme:
Exit Sub

errorme:
MsgBox "You do not have enough fields in your table to store this number "
End Function
 
Back
Top