IsNumeric validation rule

[Guest]

I have a Form with a text box called "text field eight chars" which I want to
validate as numeric. Set up validation rule as IsNumeric([text field eight
chars]). Whatever I enter into this text box when I switch to Form view I
recieve the Validation Text message as an error. I have tried several
lengths of number up to the max field size of 8 chars - always an integer,
but validation rule seems to think the input is not numeric.

I'm new to Access so tried IsNull and IsDate to check my syntax and these
work fine.

Any help would be appreciated.

Thanks, Harry H.

 

[Ken Snell \(MVP\)]

Try something like this in the text box's BeforeUpdate event:

Private Sub TextboxName_BeforeUpdate(Cancel As Integer)
Dim strTest As String, lngL As Long
Const strNum As String = "[0-9]"
strTest = ""
For lngL = 1 To 8
strTest = strTest & strNum
Next lngL
If Not Me.TextboxName.Value Like strTest Then
MsgBox "Not a valid entry."
Cancel = True
End If
End Sub

 

[Marshall Barton]

I have a Form with a text box called "text field eight chars" which I want to
validate as numeric. Set up validation rule as IsNumeric([text field eight
chars]). Whatever I enter into this text box when I switch to Form view I
recieve the Validation Text message as an error. I have tried several
lengths of number up to the max field size of 8 chars - always an integer,
but validation rule seems to think the input is not numeric.

I'm new to Access so tried IsNull and IsDate to check my syntax and these
work fine.

I don't know why IsNumeric isn't accepting anything, but
even if it did work, it's probably too general to do what
you want. IsNumeric accepts anything that can be converted
to any kind of number, e.g. IsNumeric("2d-3") is true
because Val("2d-3") = 0.002

A different validation rule to check for a non-negative
integer value:
Not Like "*[!0-9]*"

 

[Guest]

Ken,

Thank you. This works fine when the field contains an 8 digit integer which
is the max field size but produces an error for an entry less than the max.
Will I need to test for blanks, nulls and zero length strings as well?

Thanks, Harry H

Try something like this in the text box's BeforeUpdate event:

Private Sub TextboxName_BeforeUpdate(Cancel As Integer)
Dim strTest As String, lngL As Long
Const strNum As String = "[0-9]"
strTest = ""
For lngL = 1 To 8
strTest = strTest & strNum
Next lngL
If Not Me.TextboxName.Value Like strTest Then
MsgBox "Not a valid entry."
Cancel = True
End If
End Sub

--

Ken Snell
<MS ACCESS MVP>

I have a Form with a text box called "text field eight chars" which I want
to
validate as numeric. Set up validation rule as IsNumeric([text field
eight
chars]). Whatever I enter into this text box when I switch to Form view I
recieve the Validation Text message as an error. I have tried several
lengths of number up to the max field size of 8 chars - always an integer,
but validation rule seems to think the input is not numeric.

I'm new to Access so tried IsNull and IsDate to check my syntax and these
work fine.

Any help would be appreciated.

Thanks, Harry H.

 

[Guest]

Marshall,

Thank you very much for your comments on the IsNumeric function. It is
clearly not what I want. Have tried your solution and works fine.

Appreciate your time and thanks again, Harry H.

I have a Form with a text box called "text field eight chars" which I want to
validate as numeric. Set up validation rule as IsNumeric([text field eight
chars]). Whatever I enter into this text box when I switch to Form view I
recieve the Validation Text message as an error. I have tried several
lengths of number up to the max field size of 8 chars - always an integer,
but validation rule seems to think the input is not numeric.

I'm new to Access so tried IsNull and IsDate to check my syntax and these
work fine.

I don't know why IsNumeric isn't accepting anything, but
even if it did work, it's probably too general to do what
you want. IsNumeric accepts anything that can be converted
to any kind of number, e.g. IsNumeric("2d-3") is true
because Val("2d-3") = 0.002

A different validation rule to check for a non-negative
integer value:
Not Like "*[!0-9]*"

 

[Ken Snell \(MVP\)]

Sure - this should make it more generic:

Private Sub TextboxName_BeforeUpdate(Cancel As Integer)
Dim strTest As String, lngL As Long, lngLen As Long
Const strNum As String = "[0-9]"
strTest = ""
lngLen = Len(Me.TextboxName.Value & "")
If lngLen > 8 Then lngLen = 8
For lngL = 1 To lngLen
strTest = strTest & strNum
Next lngL
If Not Me.TextboxName.Value Like strTest Then
MsgBox "Not a valid entry."
Cancel = True
End If
End Sub

--

Ken Snell
<MS ACCESS MVP>

Ken,

Thank you. This works fine when the field contains an 8 digit integer
which
is the max field size but produces an error for an entry less than the
max.
Will I need to test for blanks, nulls and zero length strings as well?

Thanks, Harry H

Try something like this in the text box's BeforeUpdate event:

Private Sub TextboxName_BeforeUpdate(Cancel As Integer)
Dim strTest As String, lngL As Long
Const strNum As String = "[0-9]"
strTest = ""
For lngL = 1 To 8
strTest = strTest & strNum
Next lngL
If Not Me.TextboxName.Value Like strTest Then
MsgBox "Not a valid entry."
Cancel = True
End If
End Sub

--

Ken Snell
<MS ACCESS MVP>

I have a Form with a text box called "text field eight chars" which I
want
to
validate as numeric. Set up validation rule as IsNumeric([text field
eight
chars]). Whatever I enter into this text box when I switch to Form
view I
recieve the Validation Text message as an error. I have tried several
lengths of number up to the max field size of 8 chars - always an
integer,
but validation rule seems to think the input is not numeric.

I'm new to Access so tried IsNull and IsDate to check my syntax and
these
work fine.

Any help would be appreciated.

Thanks, Harry H.

 

[Guest]

Thats great, thanks Ken.

Sure - this should make it more generic:

Private Sub TextboxName_BeforeUpdate(Cancel As Integer)
Dim strTest As String, lngL As Long, lngLen As Long
Const strNum As String = "[0-9]"
strTest = ""
lngLen = Len(Me.TextboxName.Value & "")
If lngLen > 8 Then lngLen = 8
For lngL = 1 To lngLen
strTest = strTest & strNum
Next lngL
If Not Me.TextboxName.Value Like strTest Then
MsgBox "Not a valid entry."
Cancel = True
End If
End Sub

--

Ken Snell
<MS ACCESS MVP>

Ken,

Thank you. This works fine when the field contains an 8 digit integer
which
is the max field size but produces an error for an entry less than the
max.
Will I need to test for blanks, nulls and zero length strings as well?

Thanks, Harry H

Try something like this in the text box's BeforeUpdate event:

Private Sub TextboxName_BeforeUpdate(Cancel As Integer)
Dim strTest As String, lngL As Long
Const strNum As String = "[0-9]"
strTest = ""
For lngL = 1 To 8
strTest = strTest & strNum
Next lngL
If Not Me.TextboxName.Value Like strTest Then
MsgBox "Not a valid entry."
Cancel = True
End If
End Sub

--

Ken Snell
<MS ACCESS MVP>


I have a Form with a text box called "text field eight chars" which I
want
to
validate as numeric. Set up validation rule as IsNumeric([text field
eight
chars]). Whatever I enter into this text box when I switch to Form
view I
recieve the Validation Text message as an error. I have tried several
lengths of number up to the max field size of 8 chars - always an
integer,
but validation rule seems to think the input is not numeric.

I'm new to Access so tried IsNull and IsDate to check my syntax and
these
work fine.

Any help would be appreciated.

Thanks, Harry H.