Check if field contains number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Which is the easies way to find a numeric value from field. For example if
field1 contains "Device contains 12 inputs" text I would like to find "12"
value. I could do it manually checking every value with loop, but I wonder if
there is some syntax to do this easier.
 
Sam said:
Which is the easies way to find a numeric value from field. For example if
field1 contains "Device contains 12 inputs" text I would like to find "12"
value. I could do it manually checking every value with loop, but I wonder if
there is some syntax to do this easier.
Click to expand...


Use a loop. The built-in Access functions do not have that
capability. There probably are some heavy duty libraries
that can do it, but I think it would be overkill.
 
Hi Sam,

Pace Marsh, I'd use a library object, the VBScript regular expression
object. Something like this:

Public Function rGetNumber(V As Variant) As Variant
Dim oRE As Object 'VBScript_RegExp_55.RegExp
Dim oMatches As Object 'VBScript_RegExp_55.MatchCollection

rGetNumber = Null
If IsNull(V) Then 'Trap null input
Exit Function
End If

Set oRE = CreateObject("VBScript.Regexp")
With oRE
.Global = False
.IgnoreCase = False
.Multiline = False
.Pattern = "\b\d+(?:\.\d+)?\b"
'"word" boundary followed by 1 or more digits
'optionally followed by a . and 1 or more further
'digits before another boundary.
Set oMatches = .Execute(V)
End With
If oMatches.Count > 0 Then
rGetNumber = oMatches(0).Value
End If
End Function

The pattern will be more complicated if you want to handle international
number formats, thousands separators, exponents, etc. - but so would the
looping code if you used native VBA functions.
 
John said:
Pace Marsh, I'd use a library object, the VBScript regular expression
object. Something like this:
Click to expand...

Right John, but I didn't want to get into the issue of
library availability, especially one that is often removed
by cautious sys admins.
 
Back
Top