Pulling Data From Fields

[zashaikh]

Hello. I do repetitive data entry every day. So I have some VBA code that helps me do this quicker. The text I review is like this:

20164701000df201-001-00as, Taken on 01/02/2016; At the port of Seattle, Washington; Xbox; 48; EA; Valued at $999 for violation of 19TSC1595A( C ), 21USFD1, 21FGD31, 21UCDF55, 21DFR 1312.25

This is the VBA I use

Private Sub Details_AfterUpdate()
With Me
If Not IsNull(.Details) Then


.DateTaken = Mid(.Details, InStr(.Details, ";") - 10, 10)
.City = Mid(.Details, InStr(.Details, "port of ") + 8, InStrRev(.Details, "; ") - InStr(.Details, "port of ") + 9)
.City = Left(.City, (InStr(.City, ",") - 1))


This VBA automatically pulls the Date Taken and City and places them into the field I want on my form. How do I do the same for the quantity taken, in the example above, that would be "48"

[orange]

Is the format of the input always the same?
What do each of the segments mean to you?
Why are there commas and semicolons?
Do you save the data to a table(s)?

[zashaikh]

The format is always that same.
I dont care about all the segmants. Only city, date, and amount taken (which is always a number between two semi colons

Data is saved to a talble via a form

[isladogs]

Ok but is the number ALWAYS 2 digits, always preceded by Xbox and always followed by EA?
If yes, it's easy to do.
If not, we need a bit more info

Suggest when you answer you include a few more examples

[zashaikh]

The number can be from 2 to 5 digits (sometimes with a comma) but never with any alphaebets

The "xbox" can change to a hundred different things, but it is always followed by a semicolon

the number is always followed by " ;EA " or " ;TB "

[isladogs]

The number can be from 2 to 5 digits (sometimes with a comma) but never with any alphaebets

The "xbox" can change to a hundred different things, but it is always followed by a semicolon

the number is always followed by " ;EA " or " ;TB "

Sorry but that's not even true in your example which has

; EA;

i.e. semicolon space EA semicolon

20164701000df201-001-00as, Taken on 01/02/2016; At the port of Seattle, Washington; Xbox; 48; EA; Valued at $999 for violation of 19TSC1595A( C ), 21USFD1, 21FGD31, 21UCDF55, 21DFR 1312.25

[isladogs]

Based on your original example, this will work for any number of digits between 2 semicolons followed by EA or TB
I have assumed the pattern: semicolon space EA semicolon

Sub GetQuantity()

    Dim strText As String, Quantity As Integer
    
    'test string
     'strText = "20164701000df201-001-00as, Taken on 01/02/2016; At the port of Seattle, Washington; Xbox; 12248; TB; Valued at $999 for violation of 19TSC1595A( C ), 21USFD1, 21FGD31, 21UCDF55, 21DFR 1312.25"

    'get first part of string
    If InStr(strText, "EA;") > 0 Then
        strText = Left(strText, InStr(strText, "; EA;") - 1)
    ElseIf InStr(strText, "TB") > 0 Then
        strText = Left(strText, InStr(strText, "; TB;") - 1)
    End If
    
    'now remove unwanted section at start
    strText = Mid(strText, InStrRev(strText, "; ") + 1)
    
    'trim to remove spaces
    Quantity = Trim(strText)
    
    ' Debug.Print Quantity, Len(Quantity)


End Sub

[zashaikh]

Thank you. But I am having some trouble incorporating this into

"""Private Sub Details_AfterUpdate()
With Me
If Not IsNull(.Details) Then
.DateTaken = Mid(.Details, InStr(.Details, ";") - 10, 10)
.City = Mid(.Details, InStr(.Details, "port of ") + 8, InStrRev(.Details, "; ") - InStr(.Details, "port of ") + 9)
.City = Left(.City, (InStr(.City, ",") - 1))
"""

[isladogs]

Try this ....

Private Sub Details_AfterUpdate()

With Me
    If Not IsNull(.Details) Then
    .DateTaken = Mid(.Details, InStr(.Details, ";") - 10, 10)
    .City = Mid(.Details, InStr(.Details, "port of ") + 8, InStrRev(.Details, "; ") - InStr(.Details, "port of ") + 9)
    .City = Left(.City, (InStr(.City, ",") - 1))

     Dim strText As String
    
        'get first part of string
        If InStr(.Details, "EA;") > 0 Then
            strText = Left(.Details, InStr(.Details, "; EA;") - 1)
        ElseIf InStr(.Details, "TB") > 0 Then
            strText = Left(.Details, InStr(.Details, "; TB;") - 1)
        End If
    
        'now remove unwanted section at start
        strText = Mid(strText, InStrRev(strText, "; ") + 1)
    
        'trim to remove spaces
        .Quantity = Trim(strText)

       ....

End With

...

End Sub

[zashaikh]

Thank you! I forgot "Else" with "ElseIf".

Also, I included "End With" at the very bottom, right above "End Sub." I have a few other misc. lines of code dealing with some other commands in this sub. Does the placement of "End With" matter in this case? It all works well

Again, thank you very much. You really helped clear things up for me.

[isladogs]

Glad it's working for you.
One thing I forgot

In the ElseIf line add a ; immediately after TB - as I did in the EA; line
This will prevent errors if you have a word containing those letters in the Details string e.g. Football

[ssanfu]

I took a little different path.

Maybe this example dB will help....

[zashaikh]

Thanks Ridders

And ssanfu, thank you for the example db. But how does it function different from Ridders example?

[ssanfu]

As I said, it is just a different method.
I use UDfs to keep the main subroutine less complicated/cluttered.
And if I need to do that calculation in another part of the dB, I can move the function from the form module to a standard module and not have to duplicate code.



Good luck with your project.......

[isladogs]

Hi
Just to say that my original solution back in post 7 was a separate procedure GetQuantity.
In general, it is better to do that for anything that might be reused elsewhere.

Both methods work.