Validation Rules Help

[Troop]

In my booking form, that the number in putted for the nights that they want a service for doesn't go over the number of nights stayed. EG A man staying for 2 nights can't have a service of TV for 35 days as he is not staying for 35 nights. How do I do this?

https://www.dropbox.com/s/waqt49e487...0Hotel.accdb?m

[Troop]

I tried making the validation rule here it is

[Checkout]-[Arrival]<=[Internet Required (Nights)]

But for some reason it says 'Invalid SQL syntax, cannot use multiple columns in a column level CHECK constraint.'
How do I rectify this?

[June7]

Where did you try that - in textbox ValidationRule property, in a query, in macro or VBA code?

[Troop]

Where did you try that - in textbox ValidationRule property, in a query, in macro or VBA code?

I did this in the Validation Rule field.

[June7]

Try:

Is Null Or <=[Checkout]-[Arrival]

[Troop]

Doesn't work, I tried it. Comes up with the same error message. Maybe something to do with the chekout minus the arrival?

[June7]

I tested it in textbox ValidationRule property on form and it works for me, no error message.

[t. hagan]

The validation rule needs to be limited to a single field. I can see a couple of ways around this constraint.
First you could base your form on a query and use a calculated field to determine the length of the stay. (This is something that you are probably doing already. Otherwise, you could never calculate charges.) If you press the build button in a blank column, you can place the following code there:
LengthOfVisitateDiff("d", [Arrival], [Checkout])
That creates the calculated field.
Then you go to your form to create a validation rule that is based on that result, i.e. "<=[LengthOfVisit])
If you went to VBA, you could construct a message that displays when the days of internet service exceed the length of the stay, something like:
n = DateDiff("d", [Arrival], [Checkout])
If n > [Days of Internet Service] Then
msgbox "The days of internet service exceeds the length of the stay"
End IF
Good luck.

Tim Hagan, http://www.peachtek.net

[June7]

A calculated field is an option but I did test the calculation in the ValidationRule and it worked. It seemed a bit slow to respond but it did work.

[Troop]

I tested it in textbox ValidationRule property on form and it works for me, no error message.

My mistake it comes up with the error 'Syntax error missing operator in table validation expression'

[June7]

Are you doing this in the table or on form? I did it on form.

[Troop]

Ah I was doing it on the table, slap me in the face. Sorry about that. Works so woop! Ty a lot.