KANEDA 0149
asked on
ACCESS QUERY SPLIT DATA BASED ON CRITERIA
Hi,
I have a table in access with address information which the column contains a special character "|" to show where I want to split the data. There could be from 1 to 4 of these special characters "|" or none. I want to put each instance in it's own column. All the help is appreciated. Thanks!
» If the first instance of | appears to take anything to the right of this character
» If the 2nd instance of | appears to take anything to the right but if the 3rd instance appears to take anything between the 2nd instance and 3rd instance.
» If the 3rd instance of | appears to take anything to the right as well but if the 4th instance appears to take anything between the 3rd instance and 4th instance.
Below is example 1:
MC Cap Adv, LLC|Financial Life Building|4234 Brownsville Rd, Suite 129
Below is example 2:
Investment Research & Advisory|Pacific Center Plaza|80 West Peach Street|Suite 200B
I have a table in access with address information which the column contains a special character "|" to show where I want to split the data. There could be from 1 to 4 of these special characters "|" or none. I want to put each instance in it's own column. All the help is appreciated. Thanks!
» If the first instance of | appears to take anything to the right of this character
» If the 2nd instance of | appears to take anything to the right but if the 3rd instance appears to take anything between the 2nd instance and 3rd instance.
» If the 3rd instance of | appears to take anything to the right as well but if the 4th instance appears to take anything between the 3rd instance and 4th instance.
Below is example 1:
MC Cap Adv, LLC|Financial Life Building|4234 Brownsville Rd, Suite 129
Below is example 2:
Investment Research & Advisory|Pacific Center Plaza|80 West Peach Street|Suite 200B
ASKER
Thanks for the quick response Graham but I get the following error when I entered in the following SQL query.
Error Message: "Undefined function 'Split' in expression."
Undefined function <name> in expression. (Error 3085)
You entered an SQL expression that includes a Function procedure name that cannot be recognized. Make sure the function exists, that it can be used in SQL expressions, or check the expression to make sure you entered the name correctly.
SELECT A.ID, A.BILLINGSTREET, Split(A.BILLINGSTREET,"|",0) AS EXPR1
Error Message: "Undefined function 'Split' in expression."
Undefined function <name> in expression. (Error 3085)
You entered an SQL expression that includes a Function procedure name that cannot be recognized. Make sure the function exists, that it can be used in SQL expressions, or check the expression to make sure you entered the name correctly.
Create a user defined function to return the results of the Split() function.
You can invoke it in your query like this:
Public Function AddrSplitter(ByVal parmText, parmPart)
AddrSplitter = Split([parmText],"|")(parmPart)
End Function
You can invoke it in your query like this:
SELECT A.ID, A.BILLINGSTREET, AddrSplitter(A.BILLINGSTREET) AS EXPR1
I'm sorry - I could have sworn I have used Split() in a SQL query before. However, I just tried it myself and it doesn't work, but you can easily write a wrapper function in VBA:
-- Graham
Public Function GetElement( _
WholeString As Variant, _
Delimiter As String, _
Element As Integer _
) As String
On Error Resume Next
GetElement = Split(WholeString, Delimiter)(Element)
End Function
Then, in your SQL:SELECT A.ID, A.BILLINGSTREET, GetElement(A.BILLINGSTREET,"|",0) AS EXPR1
-- Graham
ASKER
Thanks Graham but I am still getting the same error message but this time with "Undefined function 'GetElement' in expression."
» I have Access 2007
» I don't see any missing reference
» I added the Public Function by inserting new Module (see below)
» I did the same SELECT query you had and it's still won't work
It must be my Access, anyway of fixing that?
» I have Access 2007
» I don't see any missing reference
» I added the Public Function by inserting new Module (see below)
Option Compare Database
Public Function GetElement( _
WholeString As Variant, _
Delimiter As String, _
Element As Integer _
) As String
On Error Resume Next
GetElement = Split(WholeString, Delimiter)(Element)
End Function
» I did the same SELECT query you had and it's still won't work
It must be my Access, anyway of fixing that?
place this codes in a regular module
to use in a query
SELECT TableAddress.Address, fnParseInfo([Address],1,"| ") AS Expr1, fnParseInfo([Address],2,"| ") AS Expr2, fnParseInfo([Address],3,"| ") AS Expr3, fnParseInfo([Address],4,"| ") AS Expr4
FROM TableAddress;
Function fnParseInfo(vString As Variant, idx As Integer, Optional Delimiter As String = "|") As String
Dim myArray() As String
myArray = Split(vString, Delimiter)
If idx < 0 Or idx > UBound(myArray) + 1 Then
fnParseInfo = ""
Else
fnParseInfo = myArray(idx - 1)
End If
End Function
to use in a query
SELECT TableAddress.Address, fnParseInfo([Address],1,"|
FROM TableAddress;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you all... it was definitely the USER (i.e. me)! I can confirm all solutions worked. Again thank you so much :)
ASKER
Thank you for the quick turnaround!
@KANEDA 0149
Did you try my code? It posted before any of the other user-defined function comments.
Did you try my code? It posted before any of the other user-defined function comments.
ASKER
Yes, that worked too. It was me, I did not contain my field with brackets.
Split([FieldName],"|")(0) will give the first section
Split([FieldName],"|")(1) will give the second section
... and so on
Graham Mandeno [Access MVP 1996-2014]