query help with varying data length

kevinh2320

New Member
Joined
May 13, 2016
Messages
31
I need help with an MS Access query expression to extract data from a string where the data varies in length. An example is shown below. Each row of data is in a single column called Field1. The first thing I need is to isolate the customer name. So... basically everything to the left of the date in the string. The second thing would be to isolate from the date portion of the string and everything to the right of it.

Field1
GENE C ANYNAME INC 09/26/2016 928.00 .00 .00 .00 BILLING ACTIVATED
UNIQUE INC 01/01/2009 760.48 .00 .00 .00 BILLING ACTIVATED
GARY P BLANKING 12/12/2018 251.00 .00 .00 .00 BILLING ACTIVATED
C&C AMERSON INC 07/01/2019 6,500.00 .00 .00 6,500.00 BILLING ACTIVATED
CRIST HOUND LODGE LLC 06/01/2019 3,041.42 .00 .00 .00 BILLING ACTIVATED
WILLIAM C JONES 02/01/2017 1,227.13 .00 .00 .00 BILLING ACTIVATED
M&M BIG/GUIDE LLC 05/01/2014 3,000.00 .00 .00 .00 BILLING ACTIVATED
GREAT ESCAPE, INC 06/13/2019 1,000.00 .00 .00 1,000.00 BILLING ACTIVATED

Thanks for any help.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,044
Office Version
2019
Platform
Windows
Using Power Query import your query result to PQ and then apply the following Mcode. If unfamiliar with PQ, then click on the links in my signature.

VBA Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Split Column by Character Transition" = Table.SplitColumn(Source, "Field1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Field1.1", "Field1.2", "Field1.3", "Field1.4", "Field1.5", "Field1.6", "Field1.7", "Field1.8", "Field1.9", "Field1.10", "Field1.11", "Field1.12"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Character Transition",{"Field1.2", "Field1.3", "Field1.4"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Field1.5", "Field1.6", "Field1.7", "Field1.8", "Field1.9", "Field1.10", "Field1.11", "Field1.12"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged.1")
in
    #"Merged Columns1"
Although this table was imported from Excel, PQ can import directly from Access.

xl2bb.xlam
ABC
1Field1.1MergedMerged.1
2GENE C ANYNAME INC 09/26/2016 928.00 .00 .00 .00 BILLING ACTIVATED
3UNIQUE INC 01/01/2009 760.48 .00 .00 .00 BILLING ACTIVATED
4GARY P BLANKING 12/12/2018 251.00 .00 .00 .00 BILLING ACTIVATED
5C&C AMERSON INC 07/01/2019 6,500.00 .00 .00 6,500.00 BILLING ACTIVATED
6CRIST HOUND LODGE LLC 06/01/2019 3,041.42 .00 .00 .00 BILLING ACTIVATED
7WILLIAM C JONES 02/01/2017 1,227.13 .00 .00 .00 BILLING ACTIVATED
8M&M BIG/GUIDE LLC 05/01/2014 3,000.00 .00 .00 .00 BILLING ACTIVATED
9GREAT ESCAPE, INC 06/13/2019 1,000.00 .00 .00 1,000.00 BILLING ACTIVATED
Sheet3
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,602
Try these:
For the name
=LEFT(A1,FIND("/",A1)-4)

For the date
=MID(A1,FIND("/",A1)-2,10)

For the first value
=MID(A1,FIND("/",A1)+9,FIND(" ",A1,FIND("/",A1)+9)-(FIND("/",A1)+9))
 

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
Something like the following should work within Access:

SQL:
SELECT Left([Field1],Len([Field1])-InStr(1,StrReverse([Field1]),"/")-6) AS CustomerName,
CDate(Mid([Field1],Len([Field1])-InStr(1,StrReverse([Field1]),"/")-5,11)) AS TransactionDate,
Right([Field1],InStr(1,StrReverse([Field1]),"/")-6) AS Details
FROM MyTable
You will need to replace 'MyTable' with your table name
 

james_lankford

Well-known Member
Joined
Jan 11, 2009
Messages
1,170
to me this looks like an import problem

how did you get this info into Access in the first place ?

it seems like you should be able to import the data so that everything doesn't end up in once field

is is fixed width ? tab delimited ? comma delimited ? did it come from excel ?
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,527
A regex solution:

Code:
Public Function ParseField(arg1, ByVal arg2 As Long) As String

Dim re As Object
Dim matches As Object
Dim i As Long
Dim j As Long
Dim m As Match

Set re = CreateObject("VBScript.RegExp")
re.Pattern = "\s*\d{1,2}\/\d{1,2}\/\d{2,4}\s*"
re.Global = False '//Find First Match - False, Find all Matches - True
re.Multiline = True
re.IgnoreCase = True
 
    If IsNull(arg1) Then
        Exit Function
    End If
 
    Debug.Print arg1
 
    Set matches = re.Execute(arg1)
    If matches.Count > 0 Then
        i = InStr(arg1, matches(0))
        j = Len(matches(0))
    End If

    If i > 0 Then
        If arg2 = 1 Then
            ParseField = Trim(Mid(arg1, 1, i - 1))
        ElseIf arg2 = 2 Then
            ParseField = Trim(Mid(arg1, i, j))
        ElseIf arg2 = 3 Then
            ParseField = Trim(Mid(arg1, i + j))
        End If
    End If
 
    Debug.Print i
 
End Function
To use put the code in a public module. It can be used in an update statement:
Code:
update Table15
     set
           Field2 = ParseField(Field1, 1),
           Field3 = ParseField(FIeld1, 2),
           Field4 = ParseField(Field1, 3)
Results of some Basic Testing:

Field1Field2Field3Field4
GENE C ANYNAME INC 09/26/2016 928.00 .00 .00 .00 BILLING ACTIVATEDGENE C ANYNAME INC
9/26/2016​
928.00 .00 .00 .00 BILLING ACTIVATED
UNIQUE INC 01/01/2009 760.48 .00 .00 .00 BILLING ACTIVATEDUNIQUE INC
1/1/2009​
760.48 .00 .00 .00 BILLING ACTIVATED
GARY P BLANKING 12/12/2018 251.00 .00 .00 .00 BILLING ACTIVATEDGARY P BLANKING
12/12/2018​
251.00 .00 .00 .00 BILLING ACTIVATED
C&C AMERSON INC 07/01/2019 6,500.00 .00 .00 6,500.00 BILLING ACTIVATEDC&C AMERSON INC
7/1/2019​
6,500.00 .00 .00 6,500.00 BILLING ACTIVATED
CRIST HOUND LODGE LLC 06/01/2019 3,041.42 .00 .00 .00 BILLING ACTIVATEDCRIST HOUND LODGE LLC
6/1/2019​
3,041.42 .00 .00 .00 BILLING ACTIVATED
WILLIAM C JONES 02/01/2017 1,227.13 .00 .00 .00 BILLING ACTIVATEDWILLIAM C JONES
2/1/2017​
1,227.13 .00 .00 .00 BILLING ACTIVATED
M&M BIG/GUIDE LLC 05/01/2014 3,000.00 .00 .00 .00 BILLING ACTIVATEDM&M BIG/GUIDE LLC
5/1/2014​
3,000.00 .00 .00 .00 BILLING ACTIVATED
GREAT ESCAPE, INC 06/13/2019 1,000.00 .00 .00 1,000.00 BILLING ACTIVATEDGREAT ESCAPE, INC
6/13/2019​
1,000.00 .00 .00 1,000.00 BILLING ACTIVATED
1/1/2001​
1/1/2001​
1/1/2001​
1/1/2001​
09/26/2016 DEF
9/26/2016​
DEF
ABC 09/26/2016ABC
9/26/2016​
ABC 1/11/2001 DEFABC
1/11/2001​
DEF
ABC 1/1/01 DEFABC
1/1/2001​
DEF
ABC 11/1/2001 DEFABC
11/1/2001​
DEF
ABC 11/1/01 DEFABC
11/1/2001​
DEF
 
Last edited:

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,527
Note that other solutions are fine as well - a good import process is always preferred since at first blush it does look like data that was once in separate columns has been smashed together when it should not have been (names, dates, balances, and statuses).
 

Forum statistics

Threads
1,078,407
Messages
5,340,048
Members
399,350
Latest member
mjpatches120

Some videos you may like

This Week's Hot Topics

Top