Hi Charles,
You can use the following function in your query:
(adapted from code posted by Dirk)
Public Function fNthElement(KeyString As Variant, _
Delimiter As String, _
ByVal ElementNo As Integer) As Variant
Dim arrSegments As Variant
If Len(Trim(KeyString & "")) > 0 Then
arrSegments = Split(KeyString, Delimiter, -1, vbTextCompare)
If ((ElementNo - 1) <= UBound(arrSegments)) _
And (ElementNo > 0) Then
fNthElement = arrSegments(ElementNo - 1)
Else
fNthElement = Null
End If
Else
fNthElement = Null
End If
End Function
Just save above function in a module,
then your query might look like:
SELECT fNthElement([somefield],"-",1) AS 1stString
FROM MyTable;
would return "BDP" for "1stString"
using your example.
You could adapt function for your "5thString"
or use
SELECT fNthElement([somefield],"-",5) & "-" & fNthElement([somefield],"-",6)
AS 5thString
FROM MyTable;
To keep from splitting the same field over again,
you might try this function:
Public Function fNthElementStatic(pKeyString As Variant, _
Delimiter As String, _
ByVal ElementNo As Integer) As Variant
Static arrSegments As Variant
Static KeyString As Variant
If pKeyString = KeyString Then
'same string
Else
KeyString = pKeyString
arrSegments = Split(KeyString, Delimiter, -1, vbTextCompare)
End If
If Len(Trim(KeyString & "")) > 0 Then
If ((ElementNo - 1) <= UBound(arrSegments)) _
And (ElementNo > 0) Then
fNthElementStatic = arrSegments(ElementNo - 1)
Else
fNthElementStatic = Null
End If
Else
fNthElementStatic = Null
End If
End Function
SELECT
fNthElementStatic([f1],"-",1) AS 1,
fNthElementStatic([f1],"-",2) AS 2,
fNthElementStatic([f1],"-",3) AS 3,
fNthElementStatic([f1],"-",4) AS 4,
fNthElementStatic([f1],"-",5) & "-" & fNthElementStatic([f1],"-",6) AS 5
FROM MyTable
Please respond back if I have misunderstood.
Good luck,
Gary Walter
Charles D Clayton Jr said:
Is it possible to use the Split Function in a query? I seem to recall
reading that some functions in A2K would not work in an expression but
that if you created your own function, then you could use it.
However, I am unable to get anything to work. I did come across Allen
Browne's explanation from 2003-10-15
microsoft.public.access.tablesdbdesign, but I could not get it to
work. It keep giving me an error.
After importing (or attaching) the spreadsheet data into a field named (say)
FullName, create a query into this table.
Enter this expression into a fresh column of the query design grid, in the
Field row:
FirstName: Split([FullName], " ")(0)
and in another column:
LastName: Split([FullName], " ")(1)
After verifying that these expressions give the right results, you can
change the query to an Update query (Update on Query menu), and place a
similar expression in the Update row under each field. Drop the alias. So
the Update row under your FirstName field will be:
Split([FullName], " ")(0)
The Split function parses the FullName field at the space.
The number in brackets specifies which word you want, where 0 is the first
word, 1 is the next, and so on.
I also seem to recall reading exactly the opposite that you could not
return an array to a query.
All I want to do is to split BDP-2-SA-A-9054-1 into 5 groups BPD, 2,
SA, A, 9054-1 and from looking at the help on Split it will do it - if
I can get it to work. Any suggestions/help?
Thanks,
Charles D Clayton Jr
