Split Function in Query

  • Thread starter Charles D Clayton Jr
  • Start date
C

Charles D Clayton Jr

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
 
G

Gary Walter

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
 
G

Gary Walter

rewrite of "static" function to handle nulls
properly (*tested*)......

Public Function fNthElementStatic(pKeyString As Variant, _
Delimiter As String, _
ByVal ElementNo As Integer) As Variant
On Error GoTo Err_fNthElementStatic
Static arrSegments As Variant
Static KeyString As Variant

If pKeyString = KeyString Then
'same string
Else
'pKeyString is new or is null
KeyString = pKeyString
If Len(Trim(KeyString & "")) > 0 Then
'pKeyString was not Null nor ZLS
arrSegments = Split(KeyString, Delimiter, -1, vbTextCompare)
Else
'pKeyString was Null or ZLS
fNthElementStatic = Null
Exit Function
End If

End If

If ((ElementNo - 1) <= UBound(arrSegments)) _
And (ElementNo > 0) Then
fNthElementStatic = arrSegments(ElementNo - 1)
Else
fNthElementStatic = Null
End If
Exit_fNthElementStatic:
Exit Function

Err_fNthElementStatic:
MsgBox Err.Description
Resume Exit_fNthElementStatic
End Function
 
C

Charles D Clayton Jr

Sorry it has taken me so long to respond. I was able to find a
function and modify it soon after I posted here. It seems to work
great, here it is for your examination:

Public Function SplitTest(Test As String, ItemNo As Integer)
On Error Resume Next
' Split into 8 parts. Each part divided by a "-"
SplitTest = Split(Test, "-", 8)(ItemNo)
End Function

There are three parts to the Split function: the field being split,
the delimiter and how many strings the field is to be split into
(optional). If you want to get all the strings then change to
SplitTest = Split(Test, "-")(ItemNo). You call this from a query
thusly: SplitField: SplitTest([fieldname],0) where [fieldname] is the
field you are splitting and 0 represents the 1st string it will be
split into. The first string will always be 0. So if you had a field
with this data "BPD-10-SA-A-5041-1" it could be divided into, at most,
6 fields with "BPD" being the first field and its ItemNo would be 0.

I sincerely hope you have a greate day today,

Charles D Clayton Jr
 
Joined
Mar 22, 2012
Messages
15
Reaction score
0
Sorry it has taken me so long to respond. I was able to find a
function and modify it soon after I posted here. It seems to work
great, here it is for your examination:

Public Function SplitTest(Test As String, ItemNo As Integer)
On Error Resume Next
' Split into 8 parts. Each part divided by a "-"
SplitTest = Split(Test, "-", 8)(ItemNo)
End Function

There are three parts to the Split function: the field being split,
the delimiter and how many strings the field is to be split into
(optional). If you want to get all the strings then change to
SplitTest = Split(Test, "-")(ItemNo). You call this from a query
thusly: SplitField: SplitTest([fieldname],0) where [fieldname] is the
field you are splitting and 0 represents the 1st string it will be
split into. The first string will always be 0. So if you had a field
with this data "BPD-10-SA-A-5041-1" it could be divided into, at most,
6 fields with "BPD" being the first field and its ItemNo would be 0.

I sincerely hope you have a greate day today,

Charles D Clayton Jr

Went with this and dug it very much, thx.

Ha! 8 years later. The machines never forget.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top