query help with varying data length

kevinh2320

Board Regular
Joined
May 13, 2016
Messages
61
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.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.

Book1
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
 
Upvote 0
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))
 
Upvote 0
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
 
Upvote 0
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 ?
 
Upvote 0
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:
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top