Solved

ACCESS QUERY SPLIT DATA BASED ON CRITERIA

Posted on 2014-03-20
11
2,803 Views
Last Modified: 2014-03-21
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
0
Comment
Question by:KANEDA 0149
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 39944255
You can use the Split() function.

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]
0
 

Author Comment

by:KANEDA 0149
ID: 39944279
Thanks for the quick response Graham but I get the following error when I entered in the following SQL query.

SELECT A.ID, A.BILLINGSTREET, Split(A.BILLINGSTREET,"|",0) AS EXPR1

Open in new window


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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39944303
Create a user defined function to return the results of the Split() function.
Public Function AddrSplitter(ByVal parmText, parmPart)
    AddrSplitter = Split([parmText],"|")(parmPart)
End Function

Open in new window


You can invoke it in your query like this:
SELECT A.ID, A.BILLINGSTREET, AddrSplitter(A.BILLINGSTREET) AS EXPR1

Open in new window

0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 39944304
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:
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

Open in new window

Then, in your SQL:
SELECT A.ID, A.BILLINGSTREET, GetElement(A.BILLINGSTREET,"|",0) AS EXPR1

Open in new window


-- Graham
0
 

Author Comment

by:KANEDA 0149
ID: 39944314
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)
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

Open in new window


» I did the same SELECT query you had and it's still won't work

It must be my Access, anyway of fixing that?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39944319
place this codes in a regular module
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

Open in new window




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;
0
 
LVL 20

Accepted Solution

by:
GrahamMandeno earned 500 total points
ID: 39944322
Hmmm ... something is clearly wrong with your setup.

Does the code compile?  (From the VBA window, go to Debug > Compile)

If you have compilation errors ANYWHERE in your code, it can prevent a query from calling a user-defined function.

-- Graham
0
 

Author Comment

by:KANEDA 0149
ID: 39944351
Thank you all... it was definitely the USER (i.e. me)!  I can confirm all solutions worked.  Again thank you so much :)
0
 

Author Closing Comment

by:KANEDA 0149
ID: 39944352
Thank you for the quick turnaround!
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39944511
@KANEDA 0149

Did you try my code?  It posted before any of the other user-defined function comments.
0
 

Author Comment

by:KANEDA 0149
ID: 39945066
Yes, that worked too.  It was me, I did not contain my field with brackets.
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question