Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 848
  • Last Modified:

Extract data from Access Table Field based on a specific pattern

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
charming
Asked:
charming
  • 2
  • 2
1 Solution
 
Patrick MatthewsCommented:
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
 
bandrieseCommented:
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
 
Patrick MatthewsCommented:
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
 
bandrieseCommented:
Nice.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now