Solved

Extract data from Access Table Field based on a specific pattern

Posted on 2008-06-20
4
840 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
  • 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views 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 Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

838 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