You cannot use split in a query. It returns an array which a query
cannot handle.
You can write a custom VBA function to extract each part and place it in
a separate field. A more complete explanation of what you want might
help to give you a good solution.
For instance, do you want to put the first value you extract into the
first column, the second value into the next column, etc. Or do you
want to put the values into one field in multiple records, So the first
value goes in record 1, 2nd into record 2, etc.
Also, are there a limited number of values. You seemed to suggest that
there were only four values - TJ, GHO, PRN, and DLV.
If you had up to four values
and the values were exactly as stated
and you wanted to put them into four new fields in your table
THEN the solution would be to use four update queries to populate the
values.
You could use a query like the following.
SELECT getSection([yourField]," ",1) as Column1
getSection([yourField]," ",2) as Column2
getSection([yourField]," ",3) as Column3
getSection([yourField]," ",4) as Column4
getSection([yourField]," ",5) as Column5
FROM [YourTable]
The function below can be copied into a VBA module and called from a
query (see above). Save the module with a name other than getSection.
The function parses a string into sections and gets a specific section /
Item / token from the string
Public Function getSection(strIn, _
Optional strDelimiter As String = ";", _
Optional intSectionNumber As Integer = 1)
'==============================================================
' Procedure : getSection
' Author : John Spencer
' Purpose : Return section of string
' Access 2000 and later
'==============================================================
Dim strArray As Variant
If Len(strIn & vbNullString) = 0 Then
getSection = strIn
Else
strArray = Split(strIn, strDelimiter, -1, vbTextCompare)
If UBound(strArray) >= intSectionNumber - 1 Then
getSection = strArray(intSectionNumber - 1)
Else
getSection = Null
End If
End If
End Function
Public Function fGetToken(strIn, _
Optional strDelimiter As String = " ", _
Optional LPos As Long = 1)
'Return the Nth item from a delimited list of items.
Dim strArr As Variant
If Len(strIn & "") = 0 Then
fGetToken = strIn
Else
strArr = Split(strIn, strDelimiter)
If LPos - 1 <= UBound(strArr) Then
fGetToken = strArr(LPos - 1)
Else
fGetToken = Null
End If
End If
End Function
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
KARL said:
As I said I know nothing of the syntax.
Sean said:
I am unable to get the Split function to work, any thoughts on syntax?
KARL DEWEY said:
Search on the function 'Split' as it should do what you want (I only know of
its existence, not what the syntax might be).
If you plan on using those new fields to search on for the char set then
there is an easier way.
Use this criteria --
Like "*" & [Enter Character String] & "*"
:
OOps, I forgot to mention that I am trying to pull out each of the char sets
to create a field for each one, so that I can then query off of each filed as
needed. So I would have one filed with all of them, ex. "TJ GHO PRN DLV" and
then a seperate field for each one, TJ, GHO, PRN, DLV. Thanks
:
Is there a way to use the in string function to pull out text if you do not
know the position of the text you are looking for? For example, if my string
shows up as "TJ GHO PRN DLVD" or "GHO PRN DLVD" as you can see, "GHO" does
not always show up in the same position within the text field. How to I pull
out "GHO" if it exist within the text field.
Thanks much,
