Need to split a single delimited field into many fields in Access

[ghnat]

I currently have 1 field in my database that has 5 different components all seperated by a delimiter (dash)
Example: abc-12345-defgh-678-ijklmno ... is the text and I need to place each component ito its own field via an Access query. Currently I export to Excel run the text to column function and then bring it back into Access. I would like to eliminate this extra step if at all possible.

Any assistance would be greatly appreciated

Gary

 

[MajP]

select split(fldName,"-")(0) as someName, split(fldName,"-")(1) as someName2,... split(fldName,"-")(4) as someName4...

 

[PHV]

MajP, you can't use the Split function in JetSQL.
In a standard code module create a function like this:

Public Function getElem(str, delim As String, N As Integer)
If IsNull(str) Then Exit Function
Dim myArr
myArr = Split(str, delim)
If N >= 1 And N <= (1 + UBound(myArr)) Then
  getElem = Trim(myArr(N - 1))
End If
End Function

And then the query:
SELECT yourField, getElem(yourField,'-',1) AS part1, ..., getElem(yourField,'-',5) AS part5
FROM yourTable

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ghnat]

Thank you PHV .. it worked like a charm amd will save me a lot of time!

Most appreciative.

Gary