query help with varying data length

[kevinh2320]

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]

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.

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.

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

 

[Jeffrey Mahoney]

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]

Something like the following should work within Access:

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]

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]

A regex solution:

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:

update Table15
     set
           Field2 = ParseField(Field1, 1),
           Field3 = ParseField(FIeld1, 2),
           Field4 = ParseField(Field1, 3)

Results of some Basic Testing:

Field1	Field2	Field3	Field4
GENE C ANYNAME INC 09/26/2016 928.00 .00 .00 .00 BILLING ACTIVATED	GENE C ANYNAME INC	9/26/2016​	928.00 .00 .00 .00 BILLING ACTIVATED
UNIQUE INC 01/01/2009 760.48 .00 .00 .00 BILLING ACTIVATED	UNIQUE INC	1/1/2009​	760.48 .00 .00 .00 BILLING ACTIVATED
GARY P BLANKING 12/12/2018 251.00 .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	C&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 ACTIVATED	CRIST 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 ACTIVATED	WILLIAM 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 ACTIVATED	M&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 ACTIVATED	GREAT 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/2016	ABC	9/26/2016​
ABC 1/11/2001 DEF	ABC	1/11/2001​	DEF
ABC 1/1/01 DEF	ABC	1/1/2001​	DEF
ABC 11/1/2001 DEF	ABC	11/1/2001​	DEF
ABC 11/1/01 DEF	ABC	11/1/2001​	DEF

 

[xenou]

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).