I have a text field that has multiple pieces of data that I need to separate.
For example 1,2,3 is inside one field. Does anyone know of a way to separate
this field into three separate records by using the comma or some other
method????
Thanks for your help in advance,
Rob
The values are always separated by a comma?
If you have Access 2000 or newer look up the Split function in VBA
help.
First add the 3 fields to your existing table.
Copy and Paste the following into a Module:
Public Function ParseText(TextIn As String, X) As Variant
On Error Resume Next
Dim var As Variant
var = Split(TextIn, ",", -1)
ParseText = var(X)
End Function
===========
Then paste this SQL into a new Query.
Change YourTable to what ever the actual table name is.
Change Field1, etc, to whatever the actual field name is you wish to
update.
Change FieldName to whatever the name of the field is that contains
the multiple value.
Update YourTable Set YourTable.Field1 = ParseText([FieldNamel],0),
YourTable.Field2 = ParseText([FieldName],1),YourTable.Field3 =
ParseText([FieldName],2);
