Force Users to Enter Time in 15-Minute Increments in a Microsoft Access Time Card, Time Sheet

Learn how to force your users to input values in 15-minute increments (or whole minutes, or whole hours, or whatever restrictions you want to set) by using a Validation Rule in Microsoft Access.

LEVEL: Expert

Sharon from Puyallup, WA (an Access Expert student) asks: I'm creating a time card in Microsoft Access. How do I restrict time entry to 15-minute increments? I was hoping to enter a validation rule that warns them they must enter time in 15-minute increments and let the user fix it, instead of automatically rounding. I don't know how to structure the validation test.

BONUS FOR CHANNEL MEMBERS:
Silver Members and up get access to an EXTENDED CUT of this video which covers additional examples. That video will show you how to round the start time down to the nearest 15-minute interval, and round the end time up to the next interval. This is great for billing customers! LOL. We'll use the AfterUpdate event and the TimeSerial function along with some good old VBA coding.

MEMBERS ONLY VIDEO:

BECOME A MEMBER:

LINKS:

ADDITIONAL RESOURCES:

WHAT DOES LEVEL MEAN:
My Access classes are divided into multiple levels. BEGINNER requires little working knowledge of Access. EXPERT assumes you know relationships and how to use functions. ADVANCED users know macros and events. DEVELOPER is full VBA programming.

KEYWORDS:
microsoft access, ms access, #msaccess, #microsoftaccess, timecard, time card, timesheet, time sheet, increments, intervals, 15-minute, 15 minutes, minute function, validation rule, validation text, timeserial

QUESTIONS:
Please feel free to post your questions or comments below. Thanks.

[nmeofdst]

instead of all the ANDs, you can put: Second([DTG])=0 And Minute([DTG]) In (0, 15, 30, 45) For these situations, I like to use a combo box (or List box depending on data) with the Possible times pre-populated. I then set the properties of the Combo box to "Limit To List" Yes and "Allow Value List Edits" No. This makes it impossible for the User to enter invalid entries, which negates the need for validation checks (as an alternative).

[resrussia]

I tried a different approach to the issue using the following validation rule:
Minute([DateTime]) Mod 15 =0.
I tested this out and it seems to work and it seems like it would be more efficient way to code the validation.
It appears that that checking for the seconds is not necessary unless the user's requirement specified seconds be entered as part of recording of the time and the number of seconds is significant for user's application.
Three possible scenario based upon your student's question:

Scenario 1: User is not required to enter seconds and the number of seconds is not significant for the application.
If the user enters 1:15, then there is no reason to check the number of seconds, because Access will assume that 1:15 is 1:15:00, exactly a 15 minute interval.

Scenario 2: User is required to enter seconds and the number of seconds is not significant for the application.
If the user enters 1:15:34 then there is no reason to check the number of seconds, because the minute part of the time is important for application is only in the minute portion of the time.

Scenario 3: User is required enter seconds and the number of seconds is significant for the application.
If the user enters 1:15:34 then it will be necessary to check for seconds because application needs the have precisely 1:15:00.
In this scenario, the validation function could look something like:
Second([Date]) And Minute([DateTime]) Mod 15 =0
In the worst case scenario, a time with 45 in it, would take more time to evaluate because Access would need to check each of the other possibilities for minute (I assume that when one the minute comparisons is true, Access stops the evaluation process). Of course this analysis depends on the relevant amount it takes Access to execute Mod function vs comparing the results of each of Minute function.
Your guidance on why your solution is better than my solution would greatly be appreciated.