Solved

Extract data from Access Table Field based on a specific pattern

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

777 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