creating a validation rule for a textbox that requires integers

  • Thread starter Thread starter Paul James
  • Start date Start date
P

Paul James

Two questions:

1. I'd like to create a validation rule for a text box in a form that only
accepts integers greater than zero. What expression can I write in the
Validation Rule property of the control to accomplish that?

Thanks in advance.

Paul
 
In my original message I said there were two questions, but I answered my
second one while I was writing the first one.

My apologies for any confusion.

Paul
 
Two questions:

1. I'd like to create a validation rule for a text box in a form that only
accepts integers greater than zero. What expression can I write in the
Validation Rule property of the control to accomplish that?
Click to expand...

The validation rule should be >0

and you could bind it to an Integer or Long Integer field.
 
Thanks, John.

Good solution. However, this text box is an unbound control, so ideally, I
wouldn't have to bind it to a field in a table. Any suggestions about how
to accomplish that?
 
Thanks, John.

Good solution. However, this text box is an unbound control, so ideally, I
wouldn't have to bind it to a field in a table. Any suggestions about how
to accomplish that?
Click to expand...

Hm. You didn't say that... <g>

Ok,
0 AND = Fix([controlname])
Click to expand...
 
Thanks for your reply again, John. However, I'm finding that adding
"=fix([controlname])" to the Validation Rule property doesn't seem to have
any effect. That is, the text box demonstrates the same behavior as it does
when I simply have the ">=" rule:

- if the user enters any number less than 0, Access displays my Validation
Text.

- if the user enters non-numeric characters, Access displays a message
saying "The value you entered isn't valid for this field."

Again, it displays these as long as >0 is in the Validation Rule, regardless
of whether the "fix()" function is present. So what is it that the fix()
function does - or is there a typo in your last message?

Paul


John Vinson said:
Thanks, John.

Good solution. However, this text box is an unbound control, so ideally, I
wouldn't have to bind it to a field in a table. Any suggestions about how
to accomplish that?
Click to expand...

Hm. You didn't say that... <g>

Ok,
0 AND = Fix([controlname])
Click to expand...
Click to expand...
 
My apologies to the group, but my last message contained a typo that may
confuse the interpretation of that message. Here's what I meant to say:
Thanks for your reply again, John. However, I'm finding that adding
"=fix([controlname])" to the Validation Rule property doesn't seem to have
any effect. That is, the text box demonstrates the same behavior as it does
when I simply have the ">0" rule:

- if the user enters any number less than 0, Access displays my Validation
Text.

- if the user enters non-numeric characters, Access displays a message
saying "The value you entered isn't valid for this field."

Again, it displays these as long as >0 is in the Validation Rule, regardless
of whether the "fix()" function is present. So what is it that the fix()
function does - or is there a typo in your last message?

Paul


ideally,
I
wouldn't have to bind it to a field in a table. Any suggestions about how
to accomplish that?
Click to expand...

Hm. You didn't say that... <g>

Ok,
0 AND = Fix([controlname])
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your reply again, John. However, I'm finding that adding
"=fix([controlname])" to the Validation Rule property doesn't seem to have
any effect. That is, the text box demonstrates the same behavior as it does
when I simply have the ">=" rule:

- if the user enters any number less than 0, Access displays my Validation
Text.

- if the user enters non-numeric characters, Access displays a message
saying "The value you entered isn't valid for this field."

Again, it displays these as long as >0 is in the Validation Rule, regardless
of whether the "fix()" function is present. So what is it that the fix()
function does - or is there a typo in your last message?
Click to expand...

Please post the actual validation rule you're using.
 
John - I'm currently using the following validation rule:
0
Click to expand...

That's it.




John Vinson said:
Thanks for your reply again, John. However, I'm finding that adding
"=fix([controlname])" to the Validation Rule property doesn't seem to have
any effect. That is, the text box demonstrates the same behavior as it does
when I simply have the ">=" rule:

- if the user enters any number less than 0, Access displays my Validation
Text.

- if the user enters non-numeric characters, Access displays a message
saying "The value you entered isn't valid for this field."

Again, it displays these as long as >0 is in the Validation Rule, regardless
of whether the "fix()" function is present. So what is it that the fix()
function does - or is there a typo in your last message?
Click to expand...

Please post the actual validation rule you're using.
Click to expand...
 
Thanks for the input on this again, John.

I noticed that you've replaced [controlname] with [fieldname], and so I'm
still puzzled over how I should handle this, since this is an unbound
control. It thus has a control name, but not a field name. Are you
implying that I might get better results with the validation if I create a
field in a table specifically for that purpose?
 
Thanks for the input on this again, John.

I noticed that you've replaced [controlname] with [fieldname], and so I'm
still puzzled over how I should handle this, since this is an unbound
control. It thus has a control name, but not a field name. Are you
implying that I might get better results with the validation if I create a
field in a table specifically for that purpose?
Click to expand...

If it's an unbound Control, you'ld probably do much better to use VBA
code in its BeforeUpdate event. I guess I was reading too hastily, and
jumping to a Field Validation Rule property.

The VBA code would be something like:

Private Sub controlname_BeforeUpdate(Cancel as Integer)
If NZ(Me!controlname) < 0 then
MsgBox "Please enter only positive numbers", vbOKOnly
Cancel = True
Elseif Val(Me!Controlname) <> Fix(Val(Me!Controlname)) Then
MsgBox "Please enter only whole numbers with no decimals", vbOKOnly
Cancel = True
End If
End Sub
 
Thanks for the help again, John.

Could you tell me what Fix() does? I looked for it in Access Help and
couldn't find it.
 
Thanks for the help again, John.

Could you tell me what Fix() does? I looked for it in Access Help and
couldn't find it.
Click to expand...

It's in the VBA help - not Access help; it truncates the fractional
part of a number without changing the datatype. I.e. if you have a
Float 3.1415926 the Fix function will return a Float 3.0000000.
 
Back
Top