|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
15-Minute Increments By Richard Rost 
6 years ago
Force 15-Minute Increments in a Time Card 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. 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. MembersI'll 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.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today! LinksValidation Rules: https://599cd.com/ValidationRule
IntroIn this video, we will talk about how to require users to enter times in 15-minute increments in Microsoft Access by using a validation rule. I will show you how to set up a DateTime field in a table, use the Second and Minute functions to restrict entries to specific intervals, and customize the validation text to give users clear instructions when their data is incorrect. This is a quick and practical guide for making sure time entries fit the format you want in your Microsoft Access database.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, we're going to talk about making sure times are entered in 15-minute increments. We're going to force users to enter times in 15-minute increments with a validation rule. Today's question comes from Sharon from Puyallup, Washington. I hope I'm pronouncing that right. One of my Access Expert students. Sharon says, 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 rule. Well, let's take a look at it. I'm going to take my blank template. You can download this from my website. It's free. There's a link down below. Let's create a table. Create table design. I'll throw an ID in here, AutoNumber, and let's just throw a DateTime in here. That's a DateTime field. I'll save this as my DateT. Okay, nice and simple. If I come over here to put dates in, I can put dates and times in, right? 1-1-20 at 6:32 p.m. Nothing stopping you. Now, validation rules are right down here. You can use validation rules to restrict this data entry. If they violate that rule, a message pops up; that's the violation text. I've got another video on how basic validation rules work. I'll put a link to it down below. Go watch that first if you've never used a validation rule before. You can do things like make sure a customer's credit limit doesn't go over $5,000 and things like that. Now, let's start off easy. Let's say I want the user to type in whole minutes. No seconds are allowed. Let's go to the validation rule, design, validation rule. There are functions to determine the day, month, year, hours, minutes, and seconds of a particular DateTime field. To make sure the user enters no seconds in, that means seconds of that DateTime have to be zero. So, Second, and notice there's a Second function. See, it says Second and a time. Put a time value in here. Now our field name is DateTime. So put in DateTime like that, close parenthesis, but be careful. As soon as I tab off it, watch what happens. It puts it in quotes. This is one of those times we have to make sure you put the brackets around your field name. I want the field DateTime, not the word DateTime. So Second, DateTime equals zero. That's my validation rule. You can enter whatever DateTime you want, as long as the seconds are zero. Let's save this. It says data integrity rules have changed. Existing data may not be valid. In other words, it's going to have to check and make sure that all the data in the table matches that validation rule. Now let's go back to table view here. If I change this to, let's say, 6:33, that's good. If I change it to 6:33 and two seconds, look at that. More values are prohibited by the validation rule. That's how you set that up. Now, Sharon wants this to be in 15-minute increments. So that means the seconds have to be zero and the minutes have to be either 0, 15, 30, or 45. How do we do that? Just like the Second function, there is a Minute function and an Hour function, and a Day function, and a Month function. I cover all these in my class, by the way. I'm going to Access Expert 11 class. I'll put a link to that down below too. So, Second has to be zero and Minute of DateTime has to also be zero. Let's leave it at that for now. This will force it to be whole hours, because Second has to be zero and Minute has to be zero. Save that. Data integrity rules have changed. Yes. Now look, it says existing data violates the new setting for the validation rule. In other words, you've got data in your table that doesn't work with that. Just cancel this. Now, I'll flip back over here and watch this. If I change this to 6:34, it yells at me. But if I change this to 6:00, now I'm good. Second is zero, minute is zero. Now we just tweak this to say here, minute is zero or minute is 15 or minute is 30 or minute is 45. In programming terms, that's an OR condition. This has to be true and a bunch of other options in here. So we're going to do this: open parenthesis. We're going to copy this to our clipboard right here (Ctrl-C) so we do not have to retype it every time. OR, minute is 15. OR, minute is 30. OR, minute is 45. Close parentheses. I can zoom in and show you all of these. Here is what you can see. There it is. Nice and big. Second is zero, and this whole thing, minute is zero or minute is 15 or minute is 30 or minute is 45. If they violate that, the validation text pops up. Make something a little nicer than what Access pops up. Right: "Time has to be entered in 15 minute increments or intervals," or whatever you want to say. Save it? Yes? Let's open it back up again. Next record, 1:15. Good. If you want military time, you can format that as well. In design view, in your format here: HH:NN. That's all you need. Short time. Save it. I'm usually just typing in the format codes. If the date portion does not matter to you: 3:30? Good. 1:31? Oh. Time has to be entered in 15 minute increments. Got to fix it. 1:45? Good. That's how you set up that validation rule. Hope that answers your question, Sharon. Want to learn more about this 15-increment stuff? Sharon didn't want to automatically round, but I do. In the extended cut, I'm going to show you how to round the start times down and the end times up. This is handy if you're building a customer. If you start work at 3:02 and you finish at 3:58, they are getting billed from 3:00 to 4:00. We'll use the After Update event and the TimeSerial function. All that's in the members-only extended cut, 11 minutes long. Silver members and up get access to all of my extended cut videos, and there are getting close to 100 of them now. How do you become a member? Click on the Join button below the video. Silver members and up get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks. But don't worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free. QuizQ1. What was the primary goal of the video tutorial?A. To automatically round times to the nearest hour B. To force users to enter times in 15-minute increments using a validation rule C. To teach how to use VBA to adjust time fields D. To show how to create reports in Access Q2. Which field type was added to the sample table to store time data? A. Text B. Number C. DateTime D. Currency Q3. What is the main purpose of a validation rule in Microsoft Access? A. To control the format of a table B. To automatically correct data entry errors C. To restrict data entry and enforce data integrity D. To rearrange records in order Q4. According to the video, which built-in Access function checks for the seconds part of a DateTime field? A. Minute B. Hour C. Second D. TimeValue Q5. What must be true in the validation rule to restrict entries to 15-minute increments? A. The Hour value must be divisible by 3 B. The Minute value must be 0, 15, 30, or 45, and seconds must be zero C. The date must be a weekend D. The field cannot be blank Q6. If a user tries to enter a time with seconds not equal to zero, what happens? A. The value is quietly rounded down B. The record is deleted from the table C. A validation error message appears D. The value is automatically set to midnight Q7. What is the correct way to reference a field named DateTime in the validation rule? A. "DateTime" B. DateTime (no quotes) C. [DateTime] D. DATE_TIME Q8. If a record is entered with 1:31 as the time, what will happen with the 15-minute increment validation rule in place? A. The record will save without an issue B. The time will be automatically changed to 1:30 C. The user will see a validation error message asking for a valid increment D. The record will be deleted Q9. Which validation text is suggested to make the error message user-friendly? A. Error: Invalid input B. Try again later C. Time has to be entered in 15 minute increments or intervals D. Only whole numbers allowed Q10. If the user wants to display military time in the table, which format should be used? A. MM/DD/YYYY B. Long Date C. HH:NN D. MMM-DD Answers: 1-B; 2-C; 3-C; 4-C; 5-B; 6-C; 7-C; 8-C; 9-C; 10-C DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone focuses on ensuring that users enter times in 15-minute increments in Microsoft Access. This topic comes up often, especially for anyone creating time cards or tracking work shifts and needing to standardize time entries.One of my students, Sharon, asked how to restrict time entry in an Access database so that users can only enter times at 15-minute intervals rather than any arbitrary minute or second. Rather than rounding times automatically, she wants Access to display a warning and allow users to correct their input. To address this, I started by creating a simple table with an AutoNumber ID and a DateTime field. In Access, you can enforce data restrictions through validation rules, which are found in the table's design view. When you set up a validation rule, Access will check that all entered data meets this rule. If an entry violates the rule, a customizable message appears, prompting the user to fix their input. If you are new to validation rules, I have another video covering the basics, which is available on my website. Some common scenarios, like preventing a customer's credit limit from exceeding a certain value, are easy to manage with validation rules. For time entries, it's a bit more complex, but still straightforward. Let's say you want to allow only whole minutes, with no seconds. In Access, each component of a DateTime value—day, month, year, hour, minute, and second—can be referenced using functions like Second, Minute, and Hour. To ensure there are no seconds, you use the Second function to check that the seconds part of your DateTime field is zero. It's important to reference the field properly, using brackets around the field name if necessary. Once this rule is saved, any entry with non-zero seconds will trigger a validation error. But Sharon's requirement is stricter: she needs both seconds to be zero and minutes to be only specific values (0, 15, 30, or 45). To add this, use the Minute function alongside the Second check. You enforce that not only must the seconds be zero, but the minutes value must be equal to one of those four values. This is done using "OR" conditions within the validation rule. You can set a custom validation message, such as "Time has to be entered in 15 minute increments or intervals," to make it clear to users why their entry was rejected. Once this rule is in place, users will only be able to enter times like 1:00, 1:15, 1:30, or 1:45. Any other entries, such as 1:31 or 6:34, will not be accepted until corrected. If you prefer to display times in military (24-hour) format, it's easy to set a custom time format (for example, HH:NN) in the field properties. In situations where only the time portion matters (not the date), this method works just as well. Any entry outside the prescribed 15-minute increments will be flagged, and the user can then adjust accordingly. For those interested in expanding on this, there are cases where you may wish to have times rounded automatically. While Sharon wanted the system to block and warn, some may find it more practical, especially for billing purposes, to automatically round the start time down and the end time up. For example, work done between 3:02 and 3:58 could be rounded for billing from 3:00 to 4:00. In the extended cut of today's lesson, I demonstrate how to accomplish this using the After Update event and the TimeSerial function. The extended cut for today's lesson runs about eleven minutes and is available to Silver members and above. Membership gives you access to a library of extended cut videos, live sessions, and other member perks, but the main TechHelp videos are always freely available. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListCreating a table for time entry in Microsoft AccessAdding a DateTime field for time card data Applying a validation rule to restrict seconds to zero Using the Second function in a validation rule Ensuring validation rule uses proper field bracket notation Testing the validation rule for seconds in table view Combining Second and Minute functions for validation Configuring a validation rule to allow only whole hours Extending the rule to permit only 15-minute increments Building an OR condition for validating allowable minutes Editing the validation text to display a custom message Formatting time entry for military time using HH:NN Testing the final validation rule for correct enforcement |
||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access timecard, time card, timesheet, time sheet, increments, intervals, 15-minute, 15 minutes, minute function, validation rule, validation text, timeserial PermaLink 15-Minute Increments in Microsoft Access |
