Solved

Extract data from Access Table Field based on a specific pattern

Posted on 2008-06-20
4
841 Views
Last Modified: 2013-11-27
I have an Access Table with a field named AdditionalForms_Names.  I need to extract data in a very specific manner from this table but am not sure how I can do it.  I have tried Text to Columns in Excel, but the pattern does not fit.  In a nutshell what I need to do is extract to a new field in a query everything to the right of the second dash - and the space of the value in that field (going left to right)  I.e. there may be 15 characters before the dash space or 10, but it is ALWAYS all the data to the right of the second dash that I am interested in

CA 20 15 - 12/04 - Mobile Equipment      should return
Mobile Equipment
WC 99 99 12A - 08/97 - Notice To Policyholder Availability Of Loss Prevention Services     should return
Notice To Policyholder Availability Of Loss Prevention Services
CA 20 78 - 09/02 - Physical Damage Coverage - Autos Held For Sale By Non-Dealers    should return
hysical Damage Coverage - Autos Held For Sale By Non-Dealers

How can I do this in a query using a formula or calling a function?  Thanks
0
Comment
Question by:charming
[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
  • 2
  • 2
4 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 200 total points
ID: 21833345
Hello charming,

Add the UDF below to your VB project, and then use it like this:

SELECT GetThird(AdditionalForms_Names, " - ")
FROM SomeTable

Regards,

Patrick
Function GetThird(StringIn As String, Delim As String, Optional MatchCase As Boolean = False) 
    Dim arr As Variant
    
    arr = Split(StringIn, Delim, 3, IIf(MatchCase, vbBinaryCompare, vbTextCompare))
    
    On Error Resume Next
    GetThird = arr(2)
    If Err <> 0 Then GetThird = ""
    
End Function

Open in new window

0
 
LVL 3

Expert Comment

by:bandriese
ID: 21833363
Try this function below. I havn't tested it, but it should work fine. Save the entire contents of the function below to a module and save it with a name other than the name of the function. In your query Your field name would be:

Somename: GetText([FieldwithTextToExtract])

If you have a large database, it may run a little slow, but should get the job done.

Option Compare Database
Option Explicit
 
Public Function GetText(InputStr As String)
 
Dim StrLenght, Counter As Integer
Dim MyString As String
Dim TempChar As String
Dim Dash As String
 
StrLenght = Len(InputStr)
MyString = ""
Dash = ""
 
For Counter = 1 To StrLenght
  
  TempChar = Mid(InputStr, Counter, 1)
 
    If (TempChar = "-") Then
        Dash = Dash + 1
      End If
    
    If (Dash > 1) Then
        MyString = MyString + TempChar
    End If
  
Next Counter
 
GetText = MyString
 
End Function

Open in new window

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 21833444
charming,

Here is a revised, slightly more flexible version.  Use it like this:

SELECT GetLast(AdditionalForms_Names, " - ", 3)
FROM SomeTable

Regards,

Patrick
Function GetLast(StringIn As String, Delim As String, Instance As Long, Optional MatchCase As Boolean = False)
    
    Dim arr As Variant
    
    arr = Split(StringIn, Delim, Instance, IIf(MatchCase, vbBinaryCompare, vbTextCompare))
    
    On Error Resume Next
    GetLast = arr(Instance - 1)
    If Err <> 0 Then GetLast = ""
    
End Function

Open in new window

0
 
LVL 3

Expert Comment

by:bandriese
ID: 21833757
Nice.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

756 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