Validation rule for text box to force >0 and also accept only integer entry

[John Doe]

I am using a text box for the user to enter a number.

The number must be >0 and it also must be an integer.

I want to create a validation rule for this but I haven't figured out
the syntax.

I have the following defined for the field (in the table):
Field Size = Long Integer
Required = Yes

If I set Validation Rule equal to >0 then it gives the error message
that I want when you enter -1.

But if you enter 1.5, then there is no error message and the entry is
rounded to 2.

I would like to generate an error message when the user enters
non-integer numbers.

 

[tina]

you could use an input mask. for instance, a mask of

0999

will allow any number from 0 to 9,999 to be entered. but it will not allow a
negative sign ( - ) or a decimal ( . ) to be entered. your validation rule
will still take care of the "greater than zero" issue. for larger numbers,
just add more "9"s at the end of the mask.

hth

 

[Todd Shillam]

Don't forget you can format input as well.

'DECLARING INTEGER VARIABLE
Dim vInput As Integer

'FORMAT AND INITIALIZE VARIABLE
vInput = Format("tboTextbox", "#")

'INITIALIZE TEXTBOX WITH VARIABLE
Me.tboTextbox = vInput

Try adjusting the 'decimal places' property for your textbox too.

Best regards,

Todd

I am using a text box for the user to enter a number.

The number must be >0 and it also must be an integer.

I want to create a validation rule for this but I haven't figured out
the syntax.

I have the following defined for the field (in the table):
Field Size = Long Integer
Required = Yes

If I set Validation Rule equal to >0 then it gives the error message
that I want when you enter -1.

But if you enter 1.5, then there is no error message and the entry is
rounded to 2.

I would like to generate an error message when the user enters
non-integer numbers.

 

[John Nurick]

Try something like

0 And Not Like "*[!0-9]*"

 

[John Doe]

Thanks for the suggestion. However, I tried this and it did not
produce the results I am looking for. This syntax still results in
Access accepting a positive non-integer and rounding it.

What I would like is to force the user to enter an integer > 0.

I set the Validation Rule to >0, which produces the validation error
message when a negative number is entered.

However, if the user enters a decimal number like 1.5 then Access
accepts it and rounds it to 2. Then the data in the database is
incorrect.

I can programmatically check this, I think, by the following:

If Fix(Me!MyControl) <> Me!MyControl Then
do error handling
End If

But all the other fileds have table-level validations on them and for
consistency I would like to place the Validation Rule in the table. I
don't know if there is a way to do that, but if there is then I must
have the sybntax wrong.

So I am looking for the syntax I could use in the field's validation
rule at the table level.

Try something like

>0 And Not Like "*[!0-9]*"

I am using a text box for the user to enter a number.

The number must be >0 and it also must be an integer.

I want to create a validation rule for this but I haven't figured out
the syntax.

I have the following defined for the field (in the table):
Field Size = Long Integer
Required = Yes

If I set Validation Rule equal to >0 then it gives the error message
that I want when you enter -1.

But if you enter 1.5, then there is no error message and the entry is
rounded to 2.

I would like to generate an error message when the user enters
non-integer numbers.

 

[John Nurick]

The validation rule I suggested works for a textbox on a form but not
for an integer field in a table.

This is because by the time the table gets to apply the rule the number
has already been converted to an integer to fit the field type. The rule
does work in tables for text fields and non-integer fields.

Thanks for the suggestion. However, I tried this and it did not
produce the results I am looking for. This syntax still results in
Access accepting a positive non-integer and rounding it.

What I would like is to force the user to enter an integer > 0.

I set the Validation Rule to >0, which produces the validation error
message when a negative number is entered.

However, if the user enters a decimal number like 1.5 then Access
accepts it and rounds it to 2. Then the data in the database is
incorrect.

I can programmatically check this, I think, by the following:

If Fix(Me!MyControl) <> Me!MyControl Then
do error handling
End If

But all the other fileds have table-level validations on them and for
consistency I would like to place the Validation Rule in the table. I
don't know if there is a way to do that, but if there is then I must
have the sybntax wrong.

So I am looking for the syntax I could use in the field's validation
rule at the table level.

Try something like

>0 And Not Like "*[!0-9]*"

I am using a text box for the user to enter a number.

The number must be >0 and it also must be an integer.

I want to create a validation rule for this but I haven't figured out
the syntax.

I have the following defined for the field (in the table):
Field Size = Long Integer
Required = Yes

If I set Validation Rule equal to >0 then it gives the error message
that I want when you enter -1.

But if you enter 1.5, then there is no error message and the entry is
rounded to 2.

I would like to generate an error message when the user enters
non-integer numbers.

 

[John Vinson]

So I am looking for the syntax I could use in the field's validation
rule at the table level.

If it's an Integer or Long Integer field, then it will be IMPOSSIBLE
to do so. Access will have converted the user's non-integer entry to
integer prior to executing the validation rule; the rule will never
even see the user's erroneous value. The Form is the only place to
trap this.

John W. Vinson[MVP]

 

[John Doe]

I see... I did not know that. Thanks for that insight. That does help,
I think. I'll maybe place the validation on the textbox control on the
form, then.

The validation rule I suggested works for a textbox on a form but not
for an integer field in a table.

This is because by the time the table gets to apply the rule the number
has already been converted to an integer to fit the field type. The rule
does work in tables for text fields and non-integer fields.

Thanks for the suggestion. However, I tried this and it did not
produce the results I am looking for. This syntax still results in
Access accepting a positive non-integer and rounding it.

What I would like is to force the user to enter an integer > 0.

I set the Validation Rule to >0, which produces the validation error
message when a negative number is entered.

However, if the user enters a decimal number like 1.5 then Access
accepts it and rounds it to 2. Then the data in the database is
incorrect.

I can programmatically check this, I think, by the following:

If Fix(Me!MyControl) <> Me!MyControl Then
do error handling
End If

But all the other fileds have table-level validations on them and for
consistency I would like to place the Validation Rule in the table. I
don't know if there is a way to do that, but if there is then I must
have the sybntax wrong.

So I am looking for the syntax I could use in the field's validation
rule at the table level.

Try something like

>0 And Not Like "*[!0-9]*"

I am using a text box for the user to enter a number.

The number must be >0 and it also must be an integer.

I want to create a validation rule for this but I haven't figured out
the syntax.

I have the following defined for the field (in the table):
Field Size = Long Integer
Required = Yes

If I set Validation Rule equal to >0 then it gives the error message
that I want when you enter -1.

But if you enter 1.5, then there is no error message and the entry is
rounded to 2.

I would like to generate an error message when the user enters
non-integer numbers.