Fitness 26
By Richard Rost
8 months ago
Custom Time Entry in Bound Date Field with Validation
In this Microsoft Access tutorial, I will show you how to handle user input for time values in a food log database, including using a text field to let users enter and edit times in a format you control. We will discuss why Access date/time fields can be tricky with formats and input masks, and I will guide you through adding a display-only text field, validating and formatting input with VBA, and updating the underlying records correctly. This is part 26.
Members
There is no extended cut, but here is the file download:
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!
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, food log, input mask, bound date field, text box workaround, food time text, before update event, IsDate validation, type conversion functions, CDate, date component check, table display field, continuous form, custom date formatting, Format function, valid date range, LogDate, account balances database
Subscribe to Fitness 26
Get notifications when this page is updated
Intro In this video, we tackle common problems with handling time and date input in Microsoft Access forms, focusing on a food log example in our fitness database. You'll see how to bypass the limitations of bound date fields and input masks by adding a short text field for formatted time entry, then using VBA code to validate and process user input with functions like IsDate and CDate. We'll cover the steps for saving the correct date and time data, ensuring proper validation, and formatting times for consistent display. This is part 26.Transcript Today is part 26 of my SoFar 26 part series on building a fitness database, and whether or not you're building a fitness database, everything covered in this database series is great for just about any database you can think of. Fitness just happens to be the backdrop. So let's get back to it.
Alright, so we are working with the food logs still, and one problem we have - by the way, I forgot I was going to mention all these buttons up here. Check this stuff out. This is part of my new calendar database that I'm working on to replace my old one. I have all these different buttons in here. You can go left and right a month, or you can go left and right a year if you want to, or go to a specific date. Type in the date you want. Or you can change the display, say three weeks. If you want more control over your buttons, I have a back and forth a week button here too. This will be in my upcoming new calendar seminar 2.0, so that will be available soon. Plus, I figured out how to get colors down here. It's going to be cool.
Alright, that was just a teaser and a little bit of advertising.
So what we're going to cover today is this guy. We can go back and forth between dates up here without a problem, but when it comes time to edit this, it gets confusing because now Access goes into this, which is the date format with the time in there. I just want the time. I don't want to see that date, but you can't do that. You can't just put in here 9 a.m. I mean, you can. But now watch what happens if I leave this and come back to it: that cereal is gone. Why? Because I put in 9 a.m. And 9 a.m. by itself is just 9 a.m. I think it's technically a date in 1890... December or something, 1899, if you put just the time in there.
We don't want that. We want to put the date in there too. But I want to just see the date there, and when I click on this, I want to just edit a date. Still let the user type in the date if they want to change what date this item is on. I want to be able to just type in 10 a.m. and have it know to put it on 10 a.m. on this date. So that's what we're going to do today. It's going to get a little bit confusing, but just stick with me. Don't worry about it.
Now, there's no easy way to do that in place with this bound date field. You can't even put an input mask on it. Watch. If you come in here and try to put an input mask to control the input... Input mask. I'll use one of the default ones. Let's just use this one. That's perfect. I'm going to do that. Okay. Do that. Alright. That's what it looks like.
Input masks are confusing. I know. Even for me. I don't remember all those formats because I barely use input masks. I don't generally like input masks. I don't spend a lot of time with that. But now look what happens. Alright. We'll come in here. And it still shows that. But even with the input mask in place - and this is one of my pet peeves of Access - the input mask is often ignored in bound date time fields. That's just a behavior of Access. I don't like it. That's just how it is. So get rid of the input mask. We're not going to use input masks.
What we are going to do instead is a little workaround that I developed way back when I built my account balances database. I'll show you that one later. Well, I'll show it to you now. Why not? Alright. Here's my account balances database. This is just a simple database that you can use. You put all your accounts in it, and you just go through once a week or whenever you want to do it. I do it a couple of times a week. You put in what your balances are, and it keeps track of what you have to have to pay.
Sometimes if you're looking at a list of items on your credit card website, you have to do math in your head or pull up the calculator or whatever. So what I wanted was a way where I could say, okay, let's click on here, and let's say I have a $120 charge. I can go minus 120 and then a $90 minus 90, and then minus 85 or whatever. Press enter and the system figures it out and does the math for you. See, but in order to do that, you have to use a trick that I'm about to show you.
I'll put a link to my account balances database down below, by the way, it's really cool.
So what we have to do is use a text box, a bound text field that displays this the way we want, receives the user's input, and then does what we want with it.
So let's start off by going to our food log table. Now I will warn you that any data that you have in here, you're going to have to run this through that. All of these dates - you shouldn't be... we just started building this. You shouldn't have too much actual live data in here yet. Don't start putting data that you care about in databases that you're building with me until I tell you that it's a good time, because we're still going to do a lot of breaking this database and doing stuff with it. So don't fall in love with your data yet.
Alright. We're still in the building phase. So what we're going to do is we're going to add right in here. I'm going to insert a row. We're going to add a field called food time text, and that's going to be a short text field. It's literally going to be only for the purposes of displaying that value. We have to bind it to the table because it's a continuous form, and that's the only way we can get it to display. If we leave it unbound, then every record will appear to have the same value.
So yes, we're saving some data in the table that is for display purposes only, and the real value will be in here still. Okay. Alright. Save it. Close it.
Now if you want to go in here and add these values in here, that's fine. You can. It's what it's going to look like. It's going to end up looking like this. This one will be 2 o'clock PM. Alright. This one - and I'm typing these in for now, but you get it - 9 o'clock AM. This one will be 11 o'clock. 9 PM. I don't care about seconds, so we'll be filtering those off. This is 4:48 PM, and this one is 8:05 PM.
Alright. So that's what it's going to look like when we get it onto the form. So let's go do that.
Alright. Here's our form. We're going to come in here. We're going to open up this guy. Let me close that. Alright. So right now, this is bound to that actual date field. We're going to change this to the food time text, the control source, and the name. And we're going to get rid of that format. We're going to handle the formatting in code. Okay. Save it.
Now what we're going to do is in this guy's before update event - where are you, before update, right there? - we're going to validate the input and make sure that it's an appropriate value.
The before update event, again, is you can look and see what the user entered and then allow them to continue or not, and cancel the event. So the first thing we're going to do is make sure it's a valid date time value. Because they can enter a date here, and if they do, we'll deal with that in a minute.
So, if Not IsDate(FoodTimeText), then they entered something that's not a date. The nice thing about the IsDate format is it'll look at a string value and determine - before you do any conversions or saving it anywhere - if that is a valid date or time. We'll just MessageBox "Invalid date time." You can do whatever else you want there as far as the messaging goes. Cancel = True will cancel the event. Exit Sub.
So now if we get down here, now we have a valid date time value. Let's see if it has a date component. I'm going to say component, because I don't want to put date part there, because DatePart is an actual function. I don't want you to get confused.
Okay. So now that we know that this text field has a valid date in it or a date or a time, now we can save that in an actual date variable in here. So:
Dim d As Date
Now we're going to say:
d = CDate(FoodTimeText)
CDate is a function that will convert a string value into a date value. There are a whole bunch of type conversion functions: CDate, CByte, CBool, CCur, etc. Here they all are. And I covered them in this video, if you want to learn more.
Alright. So the value that we want to deal with now is in d. We're going to say if they entered in a date part, that means just save that whole value in the table.
Now, how do we know if it's got a date part? There are a couple of different ways. My favorite way is just to say if d is greater than or equal to one, then it has a date component, because anything less than one is going to be just a time. One is a whole day, so one would be midnight on day two.
If that's the case, FoodDateTime, which is our date time value in the table, equals d. Otherwise, it's just a time, and we can say FoodDateTime equals whatever day we're on (LogDate, that's the date up top, that's this guy, that's LogDate) plus d. End If.
One thing you could also do in here, if you want to, is check the date range. You could say it's got to be between this set of years. For example, you don't want it too far in the past or too far in the future, because they might have entered an invalid date. So if the Year of d, because remember, d now holds that date time value, if the Year of d is less than whatever you want to make it - let's say 1900... that's going back too far. Let's go to year 2000. If it's less than 2000, or the Year of d is greater than 2100, then MessageBox "Invalid year." Cancel = True. Exit Sub.
Because sometimes they'll just type in a five, and that'll just... I mean, five is a valid date time, day five, which is back in 1899, I think still.
Alright. That should do it. Let's see. Save that. That's a lot of code, so let's debug the file once in a while. There we go. Let's come back out here. Let's close it. Close it. Open it.
Alright. Looks like I got some bogus stuff in here. Let's see... okay, good, good. Let's delete this guy. Let's change you to 1 p.m. Invalid year. Oh, okay, hang on. Anybody catch that? I put this code in the wrong spot. We do want to allow values less than that. This should be inside of the "it has a date component," right in here. So, in other words, they gave us a value (valid date), but it's outside our date range.
Let's clean this up. There. Okay. Because what I typed in here when I changed this to 5 p.m. - there, that's good, and allow that. If I go to the next record... Let's see. Previous, next. Okay. Good. It kept it.
Now, we do want to apply our own format to that. I don't want to just leave it at 5 p.m. even though that's valid. If I come in here and look at the food log, that 5 p.m., that's valid. Got it. But I want these to all be uniform.
So let's apply our own format. Let's come down here. And I'm going to say, "Let's format the text field properly.\So, food time text. The text box that I am working with equals Format, food date, time, come on, and let's put it in the format that we want: h:nn am/pm.
Let's save it. Debug, compile, come back out here. I am going to type in again, 5 p.m., and oh, macros preventing. Uh oh. What happened? We got a problem, and we'll discuss this problem in tomorrow's video.
See what I did there? I did a little cliffhanger. I knew that was going to happen. Ha ha. I have had this happen a million times before, but we'll fix it tomorrow.
Members, I am going to record tomorrow's video right now, so you can watch it in just a few minutes. But everybody else, you have to wait till tomorrow.
That is going to do it for part 26, folks. Hope you learned something. Live long and prosper. I'll see you tomorrow.
TOPICS:
Displaying time without showing the date in Access forms
Problems with bound date fields in Access
Limitations of input masks for date time fields
Workaround using a bound text field for time input
Adding a short text field to a table for display purposes
Binding a form control to a display-only text field
Validating user input in the form's Before Update event
Using IsDate to check for valid date or time
Converting text input to a Date variable using CDate
Distinguishing between date and time values in Access
Saving time input on the correct date in the table
Checking and enforcing valid date ranges
Formatting time display using the Format function in VBA
COMMERCIAL:
In today's video, we're continuing with part 26 of our series on building a fitness database, but the techniques you'll learn are useful for any Access database project. We'll discuss handling date and time input issues in food logs, specifically how to let users enter times without confusing Access's date storage. You'll see how to create a text field to capture custom time input, validate it, convert it with VBA using functions like IsDate and CDate, and format it consistently so your data stays clean. If you've struggled with input masks or quirky Access date behavior, this is the fix you've been waiting for. You will find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What issue does the presenter encounter when trying to edit only the time in an Access bound date field?
A. Access removes the associated record if only a time is entered without a date
B. Access automatically corrects the time to the current system time
C. Access duplicates the record whenever a time is entered
D. Access highlights the time in red and does not allow saving
Q2. Why is simply applying an input mask not effective for controlling user input in a bound date field in Access?
A. Input masks are only available for text fields, not date fields
B. Access ignores input masks in bound date time fields
C. Input masks prevent any user input in the field
D. Input masks always enforce a strict date and time format
Q3. What workaround does the presenter suggest for controlling date and time input on a continuous form?
A. Use a calculated field to generate a unique time value for each record
B. Add a separate short text field to save and display the time input as text
C. Convert the field to an unbound object and use recordset code to handle input
D. Use a dropdown menu to select dates and times only
Q4. Why is the new 'food time text' field created as a bound, short text field in the table?
A. To store permanently modified time values for reporting purposes
B. Because unbound controls in a continuous form reflect the same value in every row
C. In order to manipulate records using SQL statements directly
D. To automatically generate a time stamp on every data entry
Q5. What is the purpose of the 'Before Update' event in the solution presented?
A. To format the display of all date and time fields on the form
B. To check and validate user input before it updates the underlying data
C. To clear the field's value before it saves
D. To enable multi-user editing of the same record
Q6. Which VBA function is used to determine if the user input in the time text field is a valid date or time?
A. IsNumeric
B. IsNull
C. IsDate
D. IsString
Q7. What does the CDate function do in this context?
A. Checks if a string value is a valid date or time
B. Converts a string value into a date/time data type
C. Formats a date value into a string for display
D. Compares two date values for equality
Q8. How does the presenter check if a user entered only a time value and not a full date and time?
A. By comparing the value to zero
B. By checking if the value is a string
C. By determining if the value is less than one
D. By confirming the presence of seconds in the value
Q9. When only a time value is entered, how does the solution assign it an appropriate date?
A. By assigning the current system date automatically
B. By assigning midnight as the default date
C. By adding the time value to the current LogDate value
D. By appending today's date as a string
Q10. What additional validation does the presenter recommend for date inputs?
A. Ensuring the input string contains the word 'date'
B. Restricting the year to a reasonable range, such as between 2000 and 2100
C. Accepting only dates from the current year
D. Allowing only time values, not dates
Q11. What happens if the 'Year' of the entered date is outside the allowed range?
A. The value is automatically changed to the closest acceptable year
B. The event is canceled and the user sees an 'Invalid year' message
C. The form closes without saving the record
D. The value is set to null
Q12. Why does using an unbound control not work for displaying unique values in a continuous form?
A. Unbound controls do not display any data by default
B. Unbound controls show the same value for every row in a continuous form
C. Unbound controls cannot display text
D. Unbound controls only allow numerical input
Q13. Why is it important not to enter significant live data while building or modifying the database in this phase?
A. Because the database structure and logic are still subject to frequent changes and may cause data loss
B. Because Access does not support live data until the build is complete
C. Because reports cannot be generated until all data is entered
D. Because input masks need to be finalized first
Q14. Why does the presenter use the Format function on the 'food time text' field?
A. To convert the date value to a numerical index
B. To display the date and time in a consistent and readable format
C. To validate that only am or pm values are entered
D. To encrypt user input before storing in the database
Answers: 1-A; 2-B; 3-B; 4-B; 5-B; 6-C; 7-B; 8-C; 9-C; 10-B; 11-B; 12-B; 13-A; 14-B
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.Summary Today's TechHelp tutorial from Access Learning Zone continues our journey in building a comprehensive fitness database, though the techniques and lessons apply to virtually any kind of database project. Fitness is simply the example being used, but the skills we're covering can be useful no matter what type of data you are working with.
We are still focusing on the food logs section, and I want to discuss a challenge many Access users face when dealing with date and time fields. One issue is how Access displays and manages these fields when users try to enter or edit just a time value. For example, if you enter 9 a.m. without a date, Access stores this as 9 a.m. on December 30, 1899, which is not what we want in most cases. Moreover, when you try to revisit that record, the associated food log entry seems to disappear, precisely because Access is not matching up the correct date.
It would be preferable to simply have Access allow entry and editing of a time connected to whatever date is currently shown for the log entry. Ideally, a user could just type "10 a.m.," and Access would automatically store that as 10 a.m. on the selected log date. Unfortunately, there is no straightforward way to do exactly this with a bound date field in Access. Input masks are not much help here either, as they are often ignored by bound date/time fields, which is one of the program's idiosyncrasies. I personally am not a fan of input masks and rarely use them for situations like this.
Instead, I recommend a workaround that I have used successfully in other databases, such as my Account Balances database. In that case, I wanted to allow users to quickly do calculations directly in a text box, with the system automatically processing the input. The key is to introduce an auxiliary text-based field alongside your actual date/time field.
To implement this method, add a new short text field to your FoodLog table—something like "FoodTimeText." This field will be used specifically for capturing and displaying user input in a readable, flexible way. We need to bind this short text field to the form, particularly because we are working with a continuous form. This ensures that each record displays its own correct time value. If the control was left unbound, all records would show the same value.
Go ahead and put some sample values into this FoodTimeText field so you can see how it looks. Enter times in a consistent format, for example, "2:00 PM," "9:00 AM," or "4:48 PM." For displaying these times on the form, modify the form so that the relevant control is bound to your new FoodTimeText field, not the original date field. Remove any display formatting from the control, as we will handle formatting using VBA code.
Next, the important piece is to use the "BeforeUpdate" event of the FoodTimeText box. This event lets you inspect and validate whatever the user has entered before the data is saved. Start by verifying that the input is a valid date or time using the IsDate function. If the input is not valid, display an error message and cancel the update. If the entry passes that test, convert the input to a date value using the CDate function, which is one of several type conversion functions available in Access VBA.
At this point, you need to decide if the user entered a full date and time, or just a time. One efficient method is to check the value numerically; in Access, days are represented as numbers greater than or equal to 1, and just times are values less than 1. If the entry includes a date, store it as-is in the main date/time field. If the user only entered a time, combine it with the log date currently displayed and store the result.
It is also wise to enforce reasonable date ranges. For example, you might want to restrict entries to between the years 2000 and 2100, to prevent impossible values like something from the 1800s, which would result from entering a bare number. Display an error message if the year falls outside your acceptable range.
Once all this is working, make sure to apply a consistent format to the FoodTimeText field after the record is updated, so that all displayed times look uniform throughout your table and forms. This improves usability and keeps data easy to read.
At the end of this process, you will have a flexible, user-friendly way for your database users to enter, edit, and view food log times without worrying about accidentally corrupting records or entering confusing old dates.
This wraps up part 26 of our series. I hope you found this lesson useful. If you want to see exactly how all of these steps are put into action—including step-by-step screen instructions—be sure to check out the full video tutorial on my website, at the link below.
Live long and prosper, my friends.Topic List Displaying time without showing the date in Access forms
Problems with bound date fields in Access
Limitations of input masks for date time fields
Workaround using a bound text field for time input
Adding a short text field to a table for display purposes
Binding a form control to a display-only text field
Validating user input in the form's Before Update event
Using IsDate to check for valid date or time
Converting text input to a Date variable using CDate
Distinguishing between date and time values in Access
Saving time input on the correct date in the table
Checking and enforcing valid date ranges
Formatting time display using the Format function in VBA |
