Trigger MS Access Table Field Validation Text

Question

I have a field in an MS Table set to number for month number.

field type = Number 
Field Size = single
Validation Rule= (Not Is Null) or (>0 and <13)
Validation Text = The month must be between 1 and 12
Required=Yes

If no data is entered I get the system error "You must enter a value in the dim_daod.daod_month field."

I want it to trigger the Validation Text instead.

What should the Validation Rule be?

Answer 1

You want your validation rule to be

(Not Is Null) And (>0 And <13)

instead.

If you still have the Required parameter set to yes then you'll need to intercept that the "You must enter a value in the field" message on your data entry form.

Alternatively, you can set the validation at the table level using the same criteria and message

( daod_month Is Not Null) And (daod_month>0 And daod_month<13)