is numeric failure

[Trever B]

Hi Thanks in advance

Big Problem

strText = "39404D2" 6 char = D for dog


lngval = isnumeric(strText)

ingval = -1 (True) why is this true when it should be false and what is
the fix.

Thanks
Trev

 

[Stuart McCall]

Hi Thanks in advance

Big Problem

strText = "39404D2" 6 char = D for dog


lngval = isnumeric(strText)

ingval = -1 (True) why is this true when it should be false and what is
the fix.

Thanks
Trev

Looks like the IsNumeric function is interpreting your string as a
scientific-notated number, ie a number followed by an upper or lower case
"D" or "E", followed by a number equal to or less than 305.

Use this little function instead:

Function IsDigitsOnly(Value As String) As Boolean
IsDigitsOnly = Len(Value) > 0 And _
Not Value Like "*[!0-9]*"
End Function

 

[Klatuu]

IsNumeric does not return the numeric value of a string. It returns either
True -1 or Flase 0 depending on whether the string can be evaluated as a
number.

In your case, it will return True because the D character can be evaluated
as a hex number format because of the D. In fact, if you use the Val
function which converts a string to a number, it will return 3940400. The
Clng fuction which converts to a long data type will return the same value.

DO NOT use a variable named Value. Value is an Access reserved word and can
confuse Access.

What is it you are trying to accomplish, perhaps we can help if you tell us
what, we can show how.

 

[Dirk Goldgar]

IsNumeric() will return True for strings that have A-F in them if the
string
can represent hex numbers.

That's not exactly right. It's not that the string could represent
hexadecimal numbers -- for example, the characters A , B, and C are not
permitted -- but that the number could be an expression using exponential
notation, in which D, E, and F all indicate that what follows is an
exponent.

Here's a replacement function that solves this problem. Place it in a
standard module. If this is a new module, when prompted by Access to name
the
module, name it anything EXCEPT

BetterIsNumeric

Public Function BetterIsNumeric(ByVal Value As String) As Boolean

If Value Like "*[!0 9.]*" Then
BetterIsNumeric = False
Else
BetterIsNumeric = True
End If

End Function

I think the pattern for that Like expression is wrong, and should be
"*[!0-9.]*".

Note that the above function won't handle a leading or trailing sign, and
will accept as numeric strings such as "12.34.56". So it's not as
comprehensive as IsNumeric. For a better IsNumeric, I'd be inclined to
start with IsNumeric and make an additional test:

Public Function AnotherIsNumeric(ByVal Value As Variant) As Boolean

If IsNumeric(Value) Then
If Value Like "*[D-F]*" Then
AnotherIsNumeric = False
Else
AnotherIsNumeric = True
Else
AnotherIsNumeric = False
End If

End Function

 

[Dirk Goldgar]

the number could be an expression using exponential notation, in which D,
E, and F all indicate that what follows is an exponent.

Actually, "F" is not accepted as an exponential notation. IsNumeric("1F2")
will return False, where IsNumeric("1D2") and IsNumeric("1E2") will return
True.

 

[John W. Vinson]

Hi Thanks in advance

Big Problem

strText = "39404D2" 6 char = D for dog


lngval = isnumeric(strText)

ingval = -1 (True) why is this true when it should be false and what is
the fix.

Thanks
Trev

As noted downthread, you're running into some pretty ancient notation. If you
had taken FORTRAN back in the 1960's, as I did, you would know that 39404D2 is
a way of representing the number 3940400.00000000 as a double precision
number. The part before the D is the mantissa, the number or numbers after the
D (or E) are the exponent, the power of ten by which you multiply the mantissa
- for instance, 3E4 is 30000, 31415E-4 is 3.1415.

If you want to check that strText contains only the digits 0 through 9,
IsNumeric will fail for these particular cases! Try

strText Like "*[!0-9]*

This will return TRUE if strText contains any nondigit character (including D
and E).