UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Extract Data From Text String, Access 2013    
 
   
rross
post May 27 2020, 08:58 AM
Post#1



Posts: 10
Joined: 13-September 12



I want to extract just the PO number, 132943, from the lines description field text shown below. It will always be between the two "pipes" and preceded by the text PO Number: and be the 4th item in the description.

Invoice Number: 2343 | Supplier: FRANKLIN COUNTY | Invoice Line Num: 1 | PO Number: 132943 | PO Line Num: 1 | Description: Contract #10161 for professional services for July 2019 through June 2020

Thanks
(old Excel guy attempting to use Access for more tasks)
Go to the top of the page
 
theDBguy
post May 27 2020, 09:06 AM
Post#2


UA Moderator
Posts: 78,506
Joined: 19-June 07
From: SunnySandyEggo


Hi. You can either try using the Split() function or regular expression.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
jleach
post May 27 2020, 09:44 AM
Post#3


UtterAccess Administrator
Posts: 10,593
Joined: 7-December 09
From: St. Augustine, FL


There's a few ways you can do it.

Split(), as DBguy suggests, can split a string into an array based on a delimiter. In that case, you'd Split() on the | character, then loop through the elements to find the one that has PO Number. Then, at least, you've narrowed it down to a particular block.

Something like this aircode:

CODE
Dim s As String
Dim v As Variant
Dim i As Integer

v = Split("...Invoice Line Num: 1 | PO Number: 132943 | PO Line Num: 1 | Desc...", "|")
For i = 0 to UBound(v)
  s = Trim(CStr(v(i)))
  if InStr(1, s, "PO Number") <> 0 Then
    's is the "PO Number: 1234578" element
    'split again on the : this time, and grab the 2nd element:
    Result = Trim(CStr(Split(s, ":")(1)))
Next i


You can also use a combination of string manipulation such as Left, InStr, Right, Mid, etc., as well as position markers to tease out the "PO Number" start position, then the next | after that, grab everything between with Mid(), etc. I prefer Split myself.

hth


--------------------
Jack D. Leach
Founder & CEO
Dymeng Services Inc.
Business Software Solutions
Go to the top of the page
 
ADezii
post May 27 2020, 10:02 AM
Post#4



Posts: 3,090
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
It will always be between the two "pipes" and preceded by the text PO Number: and be the 4th item in the description.

  1. Given the above, it's actually a lot simpler then you realize, a single line of Code will do the trick.
    QUOTE
    Mid$(Split(strTest, "|")(3), 13)
  2. Cases in point:
    CODE
    Dim strTest As String

    'Test 1
    strTest = "Invoice Number: 2343 | Supplier: FRANKLIN COUNTY | Invoice Line Num: 1 | PO Number: 132943 |" & _
            "PO Line Num: 1 | Description: Contract #10161 for professional services for July 2019 through June 2020"
    Debug.Print Mid$(Split(strTest, "|")(3), 13)

    'Test 2
    strTest = "Invoice Number: 8875687 | Supplier: Acme Industries | Invoice Line Num: 37 | PO Number: 9876543 |" & _
            "PO Line Num: 17 | Description: Contract #9886321 for professional services rendered"
    Debug.Print Mid$(Split(strTest, "|")(3), 13)
  3. OUTPUT:
    CODE
    132943
    9876543

This post has been edited by ADezii: May 27 2020, 10:03 AM
Go to the top of the page
 
DanielPineault
post May 27 2020, 11:43 AM
Post#5


UtterAccess VIP
Posts: 7,389
Joined: 30-June 11



Just to add to the choices, RegEx can be an excellent choice here

CODE
Public Function ExtractComponent(ByVal sComponent As String, ByVal sInput As Variant) As Variant
    On Error GoTo Error_Handler
    Dim oRegEx                As Object
    Dim oMatches              As Object
    Dim oMatch                As Object

    If Not IsNull(sInput) Then
        Set oRegEx = CreateObject("VBScript.RegExp")
        With oRegEx
            .Pattern = "(" & sComponent & "\s*[:]+\s*(\w*)\s*)"
            .Global = True
            .IgnoreCase = True
            .MultiLine = True
            Set oMatches = .Execute(sInput)
        End With
        For Each oMatch In oMatches
            ExtractComponent = oMatch.SubMatches(1)
            Exit For
        Next oMatch
    Else
        ExtractComponent = Null
    End If

Error_Handler_Exit:
    On Error Resume Next
    If Not oMatch Is Nothing Then Set oMatch = Nothing
    If Not oMatches Is Nothing Then Set oMatches = Nothing
    If Not oRegEx Is Nothing Then Set oRegEx = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: ExtractComponent" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function


CODE
? ExtractComponent("Invoice Number", "Invoice Number: 2343 | Supplier: FRANKLIN COUNTY | Invoice Line Num: 1 | PO Number: 132943 | PO Line Num: 1 | Description: Contract #10161 for professional services for July 2019 through June 2020")

Returns 2343

CODE
ExtractComponent("PO Number", "Invoice Number: 2343 | Supplier: FRANKLIN COUNTY | Invoice Line Num: 1 | PO Number: 132943 | PO Line Num: 1 | Description: Contract #10161 for professional services for July 2019 through June 2020")

Returns 132943

So with this single function you can easily extract any component! This is good for the single entry value. We'd have to modify the pattern for the textual entries with spaces.

--------------------
Daniel Pineault (2010-2020 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
rross
post May 28 2020, 07:45 AM
Post#6



Posts: 10
Joined: 13-September 12



Many thanks to all for the assistance. Ended up with the following -

Test2a: Left(Mid$([LinesDesc],InStr([LinesDesc],"PO Number: ")+11),6)

Was concerned the function would be beyond my skill set.

Thanks again
Go to the top of the page
 
jleach
post May 28 2020, 09:29 AM
Post#7


UtterAccess Administrator
Posts: 10,593
Joined: 7-December 09
From: St. Augustine, FL


Hi,

This method you've chosen might not be as safe as some of the others suggested. The reason being that you're relying on a specific length of the PO number for your expression to work, which may not always be the case. Even if it should usually be that the PO Number is that many characters, it's usually a better practice to use some "variable" means of determining where to stop, such as the presence of the next known character. That way if the PO number is 2 or 20 or 200 numbers long, it still works as intended.

Cheers,

--------------------
Jack D. Leach
Founder & CEO
Dymeng Services Inc.
Business Software Solutions
Go to the top of the page
 
Gustav
post May 28 2020, 01:09 PM
Post#8


UtterAccess VIP
Posts: 2,279
Joined: 21-February 07
From: Copenhagen


This is the compact method:

CODE
OrderNumber = Val(Split(Description, "PO Number:")(1))

--------------------
Microsoft Office 365 (Access) MVP 2017 ->
Go to the top of the page
 
DanielPineault
post May 28 2020, 01:14 PM
Post#9


UtterAccess VIP
Posts: 7,389
Joined: 30-June 11



That's a beautiful solution as long as the PO Number is numeric. hat_tip.gif

--------------------
Daniel Pineault (2010-2020 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
rross
post May 29 2020, 08:02 AM
Post#10



Posts: 10
Joined: 13-September 12



I copied and pasted that into the expression builder. OrderNumber:=Val(Split(Description, "PO Number:")(1))

When I run the query the following error message is returned. "The expression you entered has an invalid . (dot) or ! operator or invalid parentheses. You have typed an invalid identifier or parentheses following the null constant."
Go to the top of the page
 
Gustav
post May 29 2020, 01:11 PM
Post#11


UtterAccess VIP
Posts: 2,279
Joined: 21-February 07
From: Copenhagen


You can't directly pick an element of an array in an expression, only in code.
So, create a wrapper:

CODE
Public Function GetOrderNumber(ByVal Description As String) As String

    On Error Resume Next
    GetOrderNumber = Val(Split(Description, "PO Number:")(1))

End Function
Then you can use this expression as a controlsource:

CODE
=GetOrderNumber([YourFieldName])

--------------------
Microsoft Office 365 (Access) MVP 2017 ->
Go to the top of the page
 
rross
post Jun 2 2020, 08:59 AM
Post#12



Posts: 10
Joined: 13-September 12



Thanks for the additional information. I will learn that skill (functions in VBA) after completing a few beginning of the month tasks. Thanks again
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th July 2020 - 09:44 AM