Solved

Extract data from Access Table Field based on a specific pattern

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

929 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now