Parse Space Delimited Text

[Guest]

Can someone help me on parsing a space delimited string within a text field?
There is no exact number of segments within each record. Example:
Record 1 = "XXX ZZZZZ YYYY dddd"
Record 2 = "XXX ZZ"
Record 2 = "XXXX ZZZZ MMMM"
I need to place each block of text into their own Field (Field1, Field2,
field3........)
thanks

 

[Jeff Boyce]

Victor

One approach might be to do the parsing in Excel, then import the data.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

For the example I created a table (TableName) with fields (FieldName -
contain the full string, Field1 To Field6)

The function will split the maon string into parts, and insert each part to
a seperate field
==============================
Function SetString()
On Error GoTo SetString_Err
Dim DB As DAO.Database, MyRec As DAO.Recordset, I As Integer


Set DB = CurrentDb
Set MyRec = DB.OpenRecordset("Select * From TableName")
While Not MyRec.EOF
MyRec.Edit
' loop through the fields, change the loop to fit your needs
For I = 1 To 6
' Split the values
MyRec("Field" & I) = Split(MyRec!FieldName, " ")(I - 1)
Next I
MyRec.Update
MyRec.MoveNext
Wend

Exit Function
SetString_Err:
If Err = 9 Then
Resume Next
Else
MsgBox Error
End If
End Function
==============================