Get the First Long Integer in a Text String 3

[uncleG]

To arrange a list in my Combo Box I use the following in my Query:

xyz: IIf([QtyRange] Is Null,0,Val(Mid(([QtyRange]),37,3)))

[QtyRange] [xyz]
0
Price Each effective for quantities 1 to 24 pieces 1
Price Each effective for quantities 25 to 49 pieces 25
Price Each effective for quantities 50 to 99 pieces 50
Price Each effective for quantities 100 to 249 pieces 100
Price Each effective for quantities 250 to 499 pieces 250

While the above works for now if a new entry is added like:
[QtyRange] [xyz]
Price Each for 1 to 24 pieces 0
Price Each for 25 to 49 pieces 0

The sort order fails as there is no 37th character.

How would I get the first Long Integer in the String regardless of its location within the String to arrange my list?
How would I get the text portion preceding the Integer?
Thanks,
UncleG

 

[dhookom]

You could create a function to retrieve the number like the following and use it like any other function.
[tt][blue] xyz:GetFirstNumber([QtyRange])[/blue][/tt]

Function GetFirstNumber(pstrText As String) As Long
    Dim intN As Integer
    Dim strReturn As String
    'you should be able to find a better way to determine _
     if there is no number in the string
    pstrText = pstrText & " 0 "
    intN = 1
    Do Until InStr(1, "0123456789", Mid(pstrText, intN, 1)) > 0
        intN = intN + 1
    Loop
    Do Until InStr("0123456789", Mid(pstrText, intN, 1)) = 0
        strReturn = strReturn & Mid(pstrText, intN, 1)
        intN = intN + 1
    Loop
    GetFirstNumber = CLng(strReturn)
End Function

Hopefully this helps you create your own function to find the string prior to the first number.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

another version

Public Function getFirstLong(varText As Variant) As Long
  Dim intChar As Integer
  If Not Trim(varText & " ") = "" Then
     Do
      If IsNumeric(Mid(varText, intChar + 1, 1)) Then
         getFirstLong = getFirstLong & Mid(varText, intChar + 1, 1)
         If Not IsNumeric(Mid(varText, intChar + 1, 2)) Then
           Exit Do
         End If
      End If
      intChar = intChar + 1
     Loop Until intChar = Len(varText) + 1
  End If
End Function

 

[HarleyQuinn]

Got a bit bored at work this morning so thought I'd do a couple of Regular Expression examples for this (just to show there's at least three ways to skin this cat along with Duane and MAjP's examples ). I've also not included error handling as there needs to be at least some challenge The below code will pick out the first number between 1 and 4 digits in length and the second will pick out all text before a 1 to 4 digit number (I'll put the second one in a hidden box incase you want to write your own version using the ideas supplied (as Duane suggested).

Function GetFirstNumber(varField) As Long

    Dim re As Object
    Dim match As Object
    
    Set re = CreateObject("VBScript.RegExp")
    
    With re
        .Global = False
        .MultiLine = False
        .Pattern = "\d{1,4}"
        Set match = .Execute(varField)
    End With
    
    GetFirstNumber = match.Item(0)
    
    Set match = Nothing
    Set re = Nothing

End Function

[white]Function GetFirstWords(varField) As String

    Dim re As Object
    Dim match As Object
    
    Set re = CreateObject("VBScript.RegExp")
    
    With re
        .Global = False
        .MultiLine = False
        .Pattern = ".*?(?=\s\d{1,4})"
        Set match = .Execute(varField)
    End With
    
    GetFirstWords = Trim(match.Item(0))
    
    Set match = Nothing
    Set re = Nothing

End Function[/white]

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[uncleG]

Thanks Duane, MajP and HarleyQuinn. Seeing 3 versions goes a long way to helping me understand Code, of course it will take me a while to digest the responses. And HarleyQuinn the disappearing Ink, nice touch.
Thanks Again,
uncleG

 

[SkipVought]

This will get you either the first or last number...

Function GetNumber(pstrText As String, Optional bFirst As Boolean = True) As Long
    Dim i As Integer, a
     
    a = Split(pstrText, " ")
    
    For i = 0 To UBound(a)
        If IsNumeric(a(i)) Then
            If bFirst Then
                GetNumber = a(i)
            Else
                bFirst = True
            End If
        End If
    Next
End Function

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[HarleyQuinn]

Skip, I'd just modify your code to leave the loop after you assign GetNumber or you'll always get the last number.

Regards

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[SkipVought]

HarleyQuinn, of course you're correct.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[HarleyQuinn]

Hmm, I'm still quiet at work...

So here's my version modifed to get either first or last number (thanks to Skip as I borrowed his variable declaration to save my fingers ). I should add that this version only picks numbers preceeded by a space.

Function GetNumberHarley(varField, Optional bFirst As Boolean = True) As Long

    Dim re As Object
    Dim match As Object
    
    Set re = CreateObject("VBScript.RegExp")
    
    With re
        .Global = True
        .MultiLine = False
        .Pattern = "\s\d{1,4}"
        Set match = .Execute(varField)
    End With
    
    GetNumberHarley = match.Item(bFirst + 1)
    
    Set match = Nothing
    Set re = Nothing

End Function

Hope this helps (well, if nothing else it helped me kill a few minutes anyway!)

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[SkipVought]

Aint't Tek-Tips wonderful! All kinds of ways to skin a cat!

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[HarleyQuinn]

Indeed...

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com