Validation Rule

Topic

New Reply

I have a table with 3 fields, text, meter, lastmeter. I want a validation rule, either in a query or a form that doesn’t allow entry in first number field to exceed the second number field plus a number.

I have tried this in a query and in a form control for the first number field. Neither of them work. I am not the least bit familiar with VBA.

expression I have tried is: <[lastmeter]+672

Meaning the number in meter cannot exceed the last meter +672.

I appreciate your assistance.

Reply | Quote

Replies

Exactly where did you put this Validation Rule? You could put it in the [meter] field in the table. In a form, you’d put code in the Form’s BeforeUpdate event to check for this.

Reply | Quote

Exactly where did you put this Validation Rule? You could put it in the [meter] field in the table. In a form, you’d put code in the Form’s BeforeUpdate event to check for this.

I tried it in the table and it didn’t work. I also tried it in the form beforeupdate and it didn’t work. I have tried it the validation rule on the form and it didn’t work.

Reply | Quote

What code did you try it in the forms BeforeUpdate event?
It should look something like this:

If Meter > LastMeter +672 Then Cancel = True

Reply | Quote

What code did you try it in the forms BeforeUpdate event?
It should look something like this:

If Meter > LastMeter +672 Then Cancel = True

I will try that.

Reply | Quote

I apologize I made an error in my original post. I am posting from home since my work computer will not load this website.

I am not using a field “lastmeter”. I am using a calculated field from an expression. I think that is the problem.
I am using this: =[table]![meter]<[query]![expr1]

Reply | Quote

Where are you using that code?
Get back if you need more help.

Reply | Quote

Where are you using that code?
Get back if you need more help.

I tried this in the validation rule of the form for the “meter” box
IIf(“Meter”>”LastMeter”+672,”Then Cancel”=True)

Then I get this error:
there is a type mismatch in the form control validation rule property

Both tables the “meter” and “last meter” are set to “number”.

Reply | Quote

If Meter and LastMeter are field (or expression) names, then they should be enclosed in square brackets. Try

IIf([Meter]>[LastMeter]+672,”Then Cancel”=True)

However, I don’t know if you can use and IIF statement in a validation rule – I tend to think not. I believe they work on the True/False concept, and you might find that you need a simpler version something like:
Me.[Meter]<=Me.[LastMeter]+672 – that presumes you are doing this at the form level. If you do it at the control level on a form it would be for the "Meter" control and would simply be <=Me.[LastMeter]+672

Try looking at form validation rules in one of the many good Access books that are out there, or take a look at Restrict data input by using Validation Rules

Reply | Quote

I meant for you to put that line of code in the form’s BeforeUpdate event

Reply | Quote