Find If a Field Contains a Number in the String

[Adam McReynolds]

I am looking to find if a field contains a number and then build a case statement depending on which number is found. The field will contain data just like this:

Repaired frequency response and grounding issues. Replaced 2 Hybrids, capacitors, and connectors. Tested MER/BER and operation to specs.

Here is my code that did not work:

    If Me.txt_work_comm1 Like "*Hybrid" Then
        'Sets up auto priced based on number of hybrids entered
        '1 hybrid
        If Me.txt_work_comm1 Like "*1" Then
            strCriteria = "repair_item = 'Charter RF Amplifier Repair + 1 Hybrid' AND profile_types = 'Alpha'"
            Me.txt_rate1 = DLookup("[flat_rate_values]", "tbl_module_repairs_client_item_details", strCriteria)
        End If
        '2 hybrids
        If Me.txt_work_comm1 Like "*2" Then
            strCriteria = "repair_item = 'Charter RF Amplifier Repair + 2 Hybrids' AND profile_types = 'Alpha'"
            Me.txt_rate1 = DLookup("[flat_rate_values]", "tbl_module_repairs_client_item_details", strCriteria)
        End If

To Summarize:
1. I need to find if the word "Hybrid" or "Hybrids" is in the field
2. Then I need to know how many to determine a price


Any help would be appreciated!

 

[jdraw]

Here' a small procedure that may help.

'---------------------------------------------------------------------------------------
' Procedure : testOct7
' Author    : mellon
' Date      : 07/10/2014
' Purpose   :Test routine to search through an array to see if contents contain the string "hybrid*"
'and if so, to determine if there is/are numeric chars in the 5 positions before "Hybrid*"
'Determine if "Hybrid*" is found
'Determine the numeric chars preceding "Hybrid*"
'Display meaningful message box
'
'This sample uses an array of 4 text strings.
'------------------------------------------------------------------------------------
'Sub testOct7()
          Dim i As Integer, k As Integer
          Dim RequiredNumber As String
          Dim s(3) As String
          Dim iPOS As Integer
10       On Error GoTo testOct7_Error

       ' test data
20        s(0) = "I found 41 hybrids"
30        s(1) = "there is no h y b rid here"
40        s(2) = "I replaced 2 hybrid capacitors and a limiter switch"
50        s(3) = "Hybrids are interesting things"
60        For k = LBound(s) To UBound(s)
70            iPOS = InStr(s(k), "hybrid")
80            If iPOS >= 3 Then                'hybrid has to start beyond position 2 if there is a number
        'loop through preceding chars to see if there is a number
90                For i = 5 To 0 Step -1
100                   If IsNumeric(Mid(s(k), iPOS - i, 1)) Then
110                       RequiredNumber = RequiredNumber & Mid(s(k), iPOS - i, 1)
120                   End If
130               Next i
        'see if a number was found
140               If Len(RequiredNumber) & "" > 0 Then
150                   MsgBox "The number is " & RequiredNumber
160               Else
170                   MsgBox "No number found"
180               End If
190           Else
200               MsgBox "Hybrid not found in text"
210           End If
220           RequiredNumber = ""    'reset the requirednumber on each k 
230       Next k

240      On Error GoTo 0
250      Exit Sub

testOct7_Error:

260       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure testOct7 of Module AWF_Related"
End Sub