0

Hello,

I'm a novice at microsoft access and I'm trying to make a validation rule to limit what can be entered in a field for hours worked. I set the field size to decimal. I set it up to have 2 digits to the right and left of the decimal point. An axample is for an 8 hour day you would have to enter 08.00. I set it up to limit it to 24 hours by putting <24 in the validation rule box, too. The real problem is I need for the time to be entered in 1/4 hour increments, such as, 6.00, 5.25, 8.50 or 7.75. I've tried a few different ways but nothing is working. I could please use anybodies help with this.

Thanks,

Dan

2
Contributors
1
Reply
2
Views
10 Years
Discussion Span
Last Post by RobStocks
0

Hello,

I'm a novice at microsoft access and I'm trying to make a validation rule to limit what can be entered in a field for hours worked. I set the field size to decimal. I set it up to have 2 digits to the right and left of the decimal point. An axample is for an 8 hour day you would have to enter 08.00. I set it up to limit it to 24 hours by putting <24 in the validation rule box, too. The real problem is I need for the time to be entered in 1/4 hour increments, such as, 6.00, 5.25, 8.50 or 7.75. I've tried a few different ways but nothing is working. I could please use anybodies help with this.

Thanks,

Dan

Hi Dan,
One way to do this is at the Access table level
Set the data type to Date/Time
Set the Format to Short Time
Set the Input Mask to Short Time (using input mask wizard)
Add validaton rule (minute([YourFieldNameHereGoesHere] Mod 15) = 0
Add validation text e.g. Minutes can only be entered in intervals of 15

The expression in the validation rule divides the minute portion of your field value by 15 and returns the remainder (using the Mod operator). If this isn't equal to zero then the validation text will be displayed and the user must correct the value entered.

Regards

Rob

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.