Is there a function "Is Numeric"

[DetRich]

Hello,

Is there an existing function available in access that will return a boolean
value, Yes or No, as to whether input to the function is all numeric or not?

I am entering a number into a form. I need a function that will simply
validate if the data entered is all numeric (any number of integer digits).
Must validate for integers, not real numbers.

TIA,
Rich

 

[Stefan Hoffmann]

hi Rich,

Is there an existing function available in access that will return a boolean
value, Yes or No, as to whether input to the function is all numeric or not?

Yes, it is called

IsNumeric()

Nice, huh?

I am entering a number into a form. I need a function that will simply
validate if the data entered is all numeric (any number of integer digits).

The only caveat is that it will recognize different formats as numeric,
e.g. 1e2 (scientific notation), which may be wrong in your application
context.

Must validate for integers, not real numbers.

Then you have to append another criteria:

IsInteger = IsNumeric(yourValueToTest) And _
CInt(yourValueToTest) = CDbl(yourValueToTest)


mfG
--> stefan <--

 

[Brendan Reynolds]

Hello,

Is there an existing function available in access that will return a
boolean
value, Yes or No, as to whether input to the function is all numeric or
not?

I am entering a number into a form. I need a function that will simply
validate if the data entered is all numeric (any number of integer
digits).
Must validate for integers, not real numbers.

There is indeed an IsNumeric() function, but it will return True for any
number, not just integers . Here's a custom function that should do what you
want ...

Public Function DigitsOnly(ByVal InputString As String) As Boolean

Const strcDigits As String = "0123456789"

Dim lngLoop As Long
Dim strChar As String

If Len(InputString) > 0 Then
DigitsOnly = True
For lngLoop = 1 To Len(InputString)
strChar = Mid$(InputString, lngLoop, 1)
If InStr(1, strcDigits, strChar) = 0 Then
DigitsOnly = False
Exit For
End If
Next lngLoop
End If

End Function

 

[John Spencer MVP]

That will error if you pass it a string that is not numeric. You will get a
type mismatch error.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[James A. Fortune]

It's not hard to put error handling in. I didn't want to talk down to Rich
by suggesting he's not capable of figuring that out himself. But if you
think it's necessary here's the full function with error handling.

Public Function isInteger(myval As Variant) As Boolean
On Error GoTo Proc_Err

isInteger = IsNumeric(myval) And (myval = Val(Int(myval)))

Exit Function

Proc_Err:
If (Err.Number = 13) Then 'data type mismatch.
isInteger = False
Else
MsgBox Err.Number & vbCrLf & Err.Description
End If
Err.Clear
End Function

Chris

I'm tempted to reply with a Lyle-style answer. Yes, I've done it - it
already is!

BTW, although the OP didn't ask for it, I suspect that the IsNumeric()
function handles Reals poorly and that Microsoft assumed that we would
write our own function if we wanted all the situations handled. Has
anyone written an IsNumeric() function that handles all of the possible
representations of Reals in VB, including things like exponential
notation and weird sign placement? If not, then can someone suggest
where to find a Backus Naur diagram for numeric types in VB so that I
can do it properly, perhaps with a different name for each numeric VB
variable type to be checked?

James A. Fortune
(e-mail address removed)

 

[John Spencer MVP]

Well, I know that isNumeric handles scientific notation, and correctly returns
true for numbers such as 1e+4 and 1e-5, as well as strings that are numbers.
For instance "1,200-" is awarded a True and if you use one of the conversion
functions it is properly rendered as -1200.

So I guess I need to see an example of what it won't handle properly. I'm
pretty sure it return false with hexadecimal representations that contain letters.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[James A. Fortune]

Well, I know that isNumeric handles scientific notation, and correctly
returns true for numbers such as 1e+4 and 1e-5, as well as strings that
are numbers. For instance "1,200-" is awarded a True and if you use one
of the conversion functions it is properly rendered as -1200.

So I guess I need to see an example of what it won't handle properly.
I'm pretty sure it return false with hexadecimal representations that
contain letters.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks, John. I don't remember the exact situation that caused an
answer I didn't like so I'll have to experiment with the function and
perhaps rediscover it. Perhaps I will discover other things in the
process as well.

James A. Fortune
(e-mail address removed)

 

[Brendan Reynolds]

Well, I know that isNumeric handles scientific notation, and correctly
returns true for numbers such as 1e+4 and 1e-5, as well as strings that
are numbers. For instance "1,200-" is awarded a True and if you use one of
the conversion functions it is properly rendered as -1200.

So I guess I need to see an example of what it won't handle properly. I'm
pretty sure it return false with hexadecimal representations that contain
letters.

One thing to watch out for (I won't call it 'incorrect') is that IsNumeric
handles currency symbols according to the locale under which the code is
running. For example, on my PC, IsNumeric("$1") will return false, but on a
PC using US settings, the same expression will return True.