Validate numeric value

[John J.]

When I use isnumeric() I notice that values like

44.
44,
44+
and maybe more special characters

are seen as numeric.

Is there an easy way to change this so that only 'real' figures are accepted
as numeric?

Thank you.
John

 

[Ken Sheridan]

You could write your own function, e.g.

Public Function IsRealNumber(strNum As String) As Boolean

Dim n As Integer
Dim strChr As String

' set return value ot True as default
IsRealNumber = True

' step through string and examine each
' character. If not a number set return
' value to False and exit loop
For n = 1 To Len(strNum)
strChr = Mid(strNum, n, 1)
If Not IsNumeric(strChr) Then
' if not decimal point
If strChr <> "." Then
IsRealNumber = False
Exit For
End If
End If
Next n

End Function

Note that this would not recognize numbers in hexadecimal notation e.g.
&H2C, or numbers expressed with an exponent, e.g. 44E2 as numbers, if these
are text rather than number data types. It would work with decimal numbers
whether they are text or a number data type, however.

Ken Sheridan
Stafford, England

 

[Dirk Goldgar]

When I use isnumeric() I notice that values like

44.
44,
44+
and maybe more special characters

are seen as numeric.

Is there an easy way to change this so that only 'real' figures are
accepted as numeric?

You'll have to define what you'll accept as a "real" number. If you want
only numeric digits, you can test for whether a string contains any
character other than 0-9, like this:

If strMaybeNum Like "*[!0-9]*" Then
Msgbox "Not all digits!"
Else
Msgbox "That looks okay."
End If

 

[John J.]

Thanks to you both! Dirk's solution is what I will implement.
John

When I use isnumeric() I notice that values like

44.
44,
44+
and maybe more special characters

are seen as numeric.

Is there an easy way to change this so that only 'real' figures are
accepted as numeric?

You'll have to define what you'll accept as a "real" number. If you want
only numeric digits, you can test for whether a string contains any
character other than 0-9, like this:

If strMaybeNum Like "*[!0-9]*" Then
Msgbox "Not all digits!"
Else
Msgbox "That looks okay."
End If

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)