TimeValue
By Richard Rost
4 years ago
Get Time Only from a DateTime with TimeValue
In this Microsoft Access tutorial, I'll show you how to use the TimeValue function to isolate the time part (only) of a DateTime value in Microsoft Access. This is handy if you have a lot of dates that include times but you only care about the time. We will then use the TimeValue function along with a Validation Rule to make sure that appointments are entered between 9 am and 5 pm for any given day.
Pre-Requisites
Recommended Courses
Links
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, just the time, convert date/time to time only, get time from a datetime, datetime field time only
Subscribe to TimeValue
Get notifications when this page is updated
Intro In this video, I will show you how to use the TimeValue function in Microsoft Access to extract just the time from a date/time value and apply a validation rule to ensure appointments are only entered between 9 a.m. and 5 p.m. We will walk through building an appointment table, demonstrate why storing dates and times in the same field is helpful, and set up Access to prevent entries outside business hours with a user-friendly validation message.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, we're going to learn about the Time Value Function, which is just like the Date Value Function, except it gives you the time instead of the date. We are going to use that to enter appointments, but we want to make sure that they are entered between 9 a.m. and 5 p.m. of whatever given day they are on.
For example, if my secretary goes to put an appointment in and she puts in 7 p.m., we will say, nope, can't do that. It has to be between 9 to 5. There is a really easy way to do it. I am going to show you that now.
First, if you haven't watched my Date Value video, go watch that first. It explains how that function works, which is very similar to this one. I am not going to repeat a lot of the stuff I said in that one, so watch that if you have no clue what I'm doing.
I am going to show you how the Time Value Function works, but we are also going to use a validation rule to monitor what's entered into the table as far as when the user types in the date and time.
If you want to learn how that works and you've never used a validation rule before, go watch this video. Those are all free videos on my website and YouTube channel, and you can watch them whenever you want. You will find links you can click on down below.
This is my TechHelp Free template, my sample database. You can grab a copy of this off my website as well. It is absolutely free.
Let's take a look at some date and time values. Let's go to the Contact Table. The contacts are every time you talk to somebody, you put a contact in the system. We've got the Contact Date and Time, and that is a date/time value, date/time field. Sometimes you want to pull out just a date, and sometimes you want to pull out just a time.
We learned about the Date Value Function in the last video. Today, we're going to do the Time Value Function. Let's make a query: Create, Query Design. Let's say I'm going to bring in my Contact Table, bring in the Contact Date, and we're going to come right here. I'll zoom in for you, Shift+F2, so you can see what I'm doing.
I am going to say the Date Only is DateValue([Contact Date]). OK. I'll tab over here, and I'll Shift+F2 again, and I'll say the Time Only is TimeValue([Contact Date]).
We're going to pull those apart. Give me the date. Give me the time. Give me an L. Oh, give me an R. I don't know what I'm doing.
Let's run that query. There you go. It's that simple. I've separated out the date and the time.
Now, why and when is this useful? There are a lot of different things you can do with this. One thing that I like to do is to make sure that a date/time value that's entered is within a particular range of times, like the example I mentioned earlier.
Let's say you're doing your appointment calendar and you want to make sure that whoever is putting your appointments in for you puts in something between 9 and 5. You don't want someone scheduling an accidental 10 p.m. appointment or a 2 a.m. appointment. This is a good check for that.
Let's make a quick appointment table. Create, Table Design. Appointment ID, that's my AutoNumber. I am OK with abbreviating long words like appointment. Just be consistent.
AppointmentDateTime. Now remember, "date" and "time" individually are keywords, so we never want to have a field called "date" or a field called "time." I see this all the time. They are reserved words in Access.
I've got a video coming out on reserved words very soon. I have been working on it for a while. In the meantime, Alex, my right-hand man, has been compiling a glossary entry here with all the different reserved words, and I'll be covering the big ones and why you shouldn't use them. If you want the full list, I'll put a link down below to the reserved words list.
Now, DateTime by itself is a reserved word, but it's not one of the major ones. It doesn't usually give you problems, but I still like to put something in front of it, like SomethingDateTime - ContactDateTime, OrderDateTime, that kind of stuff. OrderDate if you just want a date.
Anyway, this is going to be a date/time value. Remember, dates and times are also together in the same field. You could do a separate date field and a separate time field, and that's an easier way that a lot of beginners will get over this problem by having two separate fields, but that also adds some problems too.
Let me just show you this. Description. OK, done with this table. That's my appointment table, primary key, yes.
Now, as of right now, there are no restrictions on here whatsoever. Anybody can just type in whatever they want. I can come in here and say, today's date, I'll hit Control+semicolon and put today's date in there. Then I'll put in 9 a.m., tab. That's me.
Again, we've got nothing in here to restrain us from entering 9 p.m., so that's where the validation rule comes into play. If you have watched the validation rule video, you know what that means.
Let's take a look at how we set that up. If I come down here to the validation rule, and if this field only had time values in it, you could say something like this: Between 9:00 AM and 5:00 PM. Remember, we put our dates and times inside of those little pound signs. I'll zoom in so you can see that. Dates and times go between those.
Now, let me save it. It says, "Data integrity rules have changed. Existing data may not be valid for the new rules. Do you want me to check it?" Go ahead and check things for me. It says, "Existing data violates the new setting." In other words, you've got stuff in your table that doesn't work with that validation rule.
I am going to stop testing and hit cancel. Now, you can still save the table at this point. Let's look at our data. That is 9 a.m. What do you mean, is that not between 9 a.m. and 5 p.m.? Well, because the validation rule doesn't take the date into consideration, just the time.
Interestingly enough, if you come in here now and type in today's date at, let's say, 10 p.m., watch what message comes up. "One or more values prohibited by the validation rule. Between 12/30/1899 9:00 AM and 12/30/1899 17:00." I'll zoom in.
If you don't specify a date in your validation rule, it gives you zero day, which is December 30, 1899. Why did they pick that day? I don't know. That's just what they did.
You've got to make sure you have a date component there as well, or you can just store only time values in there and use two fields for that. It's a perfectly fine setup for beginners if you want to do that. I've had people build databases like that before, and they were perfectly fine. You store the date in one field, you hit tab, and you put the time in the next field. That's valid.
Later on, if you want to put them together, you just simply add them, and then you've got the complete date/time value. But we're a little past that now, so we're going to use a bit more advanced method. I like to store dates and times together in the same field if possible.
So what do we do? Let's hit Escape, cancel out of that, and go back to Design View.
Now, we know how to split off the time value from a date/time field by using the Time Value Function. Can we use that in the validation rule? Yes. Let's give it a try.
Shift+F2, zoom back in. Now we have to write this a slightly different way. I am going to say: TimeValue([AppointmentDateTime]) >= #9:00 AM# And TimeValue([AppointmentDateTime]) <= #5:00 PM#.
Say, take this date/time, pull out its time, and the whole thing has to be greater than or equal to 9 a.m., and has to be less than or equal to 5 p.m.
Be very careful and notice what just happened. This gets a lot of people. You see it? Let me zoom in so you can see it closer. This is a little more advanced, and a lot of people wonder why this stuff does not work sometimes. Access thought it was being helpful and it put quotes around that field name. It is not.
Normally I tell you, especially in my beginner classes, don't put spaces in your field names, because then you have to put brackets around everything. There are some cases where you still have to use those brackets because Access is trying to guess what you mean, and it thinks you want a set of quotes there. You don't. You want the field value.
So even though we do not have spaces, we've still got to put the brackets on it. Be aware of that. That happens a lot. I see that in a lot of questions people send me.
Now, that's what we want. That's our final formula. Let's save that.
Data integrity rules have changed. Say yes. Everything's good this time. The value that we got in there is good. Let's put in another record. Let's say today's date at 12 p.m., lunch with Kirk. That's good. Let's try another one, today's date at 10 p.m. "One or more values prohibited because the time value is greater than that and less than that."
You still see the old dates in there, but that's OK because the time value gets pulled out and that time value is going to be 12/30/1899 at 10 p.m.
Now you don't want this big long message because your end user is going to look at it and go, "What? Huh? 1899?" So we're going to give them a friendly message.
I've got to give this a couple of times. Come back in, Design View. Validation Text: "Appointments must be between 9 a.m. and 5 p.m. Thank you for your cooperation." Save it.
Back in here, and now let's try that Rush concert again, 9 p.m: "Appointments must be between 9 a.m. and 5 p.m. Thank you." Let's go back to 4 p.m., and we're good. Now I've got a battle drill, and we're good.
There you go. If you like this date/time stuff, I've got a lot more classes on it. I've got a two-parter, Access Expert 27 and 28. In this 20-something series, I cover all the functions in Access, all of them. 27 and 28 are the date/time functions.
This is part one. In 28, part two, we go over all the different date functions, how to play ordinals, calculate someone's age, show birthdays for next month, and many more examples. There are lots of cool functions.
If you really want to learn more, I've got the Access Date/Time Seminar, and there is a ton of stuff, all kinds of extra material I came up with when I was doing Expert 27 and 28 that I did not have time to cover in those.
We do a holiday table to figure out how many business days there are between two dates. There is a function in Excel called NETWORKDAYS, where if you give it a Tuesday and the following Wednesday, it will calculate that there are six business days, pulling out Saturday and Sunday. Holidays like Christmas and New Year's Day are easy to figure out, but if you want to figure out something like Labor Day, the first Monday in September, or Thanksgiving - well, I had to make a table so you can put that in.
So there you go. There is Time Value, and you sat through my little advertising portion of the video. Hope you enjoyed. I hope you learned something. I had a lot of fun, and I'll see you next time.Quiz Q1. What is the main purpose of the TimeValue function in Microsoft Access?
A. To extract only the date from a date/time value
B. To extract only the time from a date/time value
C. To combine a date and a time into one field
D. To format dates in a specific string
Q2. Why would you use a validation rule with a date/time field in Access?
A. To prevent duplicate appointments
B. To restrict entries to a specific time range, like 9 a.m. to 5 p.m.
C. To format the time values for display
D. To automatically sort records by time
Q3. Which of the following would be a proper validation rule to limit appointments to between 9 a.m. and 5 p.m. using the TimeValue function?
A. TimeValue([AppointmentDateTime]) >= 9:00 PM And TimeValue([AppointmentDateTime]) <= 5:00 AM
B. DateValue([AppointmentDateTime]) >= 9:00 AM And DateValue([AppointmentDateTime]) <= 5:00 PM
C. TimeValue([AppointmentDateTime]) >= 9:00 AM And TimeValue([AppointmentDateTime]) <= 5:00 PM
D. [AppointmentDateTime] Between #9:00 AM# And #5:00 PM#
Q4. What is a pitfall mentioned regarding field names in Microsoft Access?
A. Always avoid using underscores in field names
B. Never use spaces or reserved words as field names
C. You must always use all uppercase letters in field names
D. Underscores are required in every field name
Q5. What happens if you do not specify a date in the validation rule for a time-value comparison?
A. Access will generate a random date
B. The rule will be ignored by Access
C. Access uses December 30, 1899 as the base date
D. The validation rule prompts the user to enter a date
Q6. What should you do to provide a user-friendly message when validation fails?
A. Change the field type to text
B. Edit the Validation Text property with a custom message
C. Add more validation rules
D. Remove the validation rule entirely
Q7. What was suggested as a beginner approach to dealing with date and time fields?
A. Store date and time in a single field only
B. Store only the date and ignore the time
C. Use two separate fields: one for date, one for time
D. Always store both date and time as text
Q8. Why is 'Date' or 'Time' not recommended as field names in Access?
A. They are too long
B. Access treats them as reserved keywords, which can cause problems
C. They are not descriptive enough
D. They are not allowed by Access at all
Q9. How do you split a date/time field into just the date and just the time in a query?
A. Using DateValue and TimeValue functions on the field
B. By creating separate fields in the table
C. By using the Format function only
D. By exporting the data to Excel
Q10. What does the video recommend if you want more advanced examples of date/time calculations in Access?
A. Watch the Access Security Seminar
B. Take Access Expert 27 and 28 for more on date/time functions
C. Only use built-in Excel formulas
D. Use macros instead of queries
Answers: 1-B; 2-B; 3-C; 4-B; 5-C; 6-B; 7-C; 8-B; 9-A; 10-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 video from Access Learning Zone covers the Time Value function in Microsoft Access, which works much like the Date Value function but extracts just the time portion from a date and time value. This can be very useful in situations where you are managing appointments and need to ensure they are entered only during specific hours, such as standard business hours from 9 a.m. to 5 p.m. For example, if someone attempts to enter an appointment for 7 p.m., you want the system to prevent it, enforcing that rule simply and effectively.
If you have not already learned how the Date Value function works, I recommend starting with my video on that topic. The Date Value and Time Value functions share very similar logic, and understanding one will help with the other. I will not be repeating all of those details here, so definitely review that resource first if you need a refresher.
In this lesson, we look at using the Time Value function in conjunction with a validation rule. The validation rule will monitor what gets entered into your table, specifically focusing on the time included in the date and time value. If you are new to validation rules, check out my earlier video on that subject for a solid foundation. All of these videos are available for free on my website and YouTube channel.
I am working with my TechHelp Free template database, which you can download for free from my website. In the Contact Table, each time you have a communication with someone, you log it with a date and time field. Sometimes, you might just want the date, and other times, you might want just the time.
Previously, we looked at extracting the date using Date Value. Now, we are going to do something similar with Time Value. In a query, you can add calculated fields to pull out either the date or time from a date and time value. For example, one field could use the Date Value function to show only the date, while another uses Time Value to display just the time. Running the query reveals that these functions easily separate the date and time for each record.
Why is this helpful? One application is to ensure that appointments fall within acceptable hours, like 9 a.m. to 5 p.m., and that no one accidentally books something at an inappropriate time such as 10 p.m. or 2 a.m.
Let's walk through setting this up with a new Appointment table. When designing your table, be careful not to name fields using reserved words like "date" or "time." For example, use "AppointmentDateTime" instead of just "date" or "time." Using reserved words can cause issues in Access, and I am working on a video about this topic. You can also find a glossary on my site that covers reserved words and why to avoid them.
In your new table, include an AutoNumber field for your primary key and a date and time field such as AppointmentDateTime. While beginners sometimes keep separate fields for date and time, storing them together in one field is generally better and more efficient.
At this point, there is nothing stopping you from entering any date and time into the AppointmentDateTime field. You could put in a valid value like today at 9 a.m., or you could also put in an invalid value like 9 p.m. That is where a validation rule becomes important. If you have learned about validation rules in my other videos, you know they set boundaries on what users can enter.
If your field stored only a time value, you could directly set the validation rule as "Between 9:00 AM and 5:00 PM." Remember, in Access, dates and times in validation rules are enclosed in pound signs. However, when trying this, you may find issues when your data includes both date and time. The validation rule may not work as expected because Access tries to match both the date and time parts, defaulting to a date of December 30, 1899 if you leave out the date. This can result in confusing messages for your users.
You can circumvent some of these complications by keeping the date and time in separate fields if you prefer, but eventually, you will want to work with them together for more advanced functionality. The next step is to use the Time Value function in your validation rule. This allows you to extract just the time from the date and time value, enforcing your restriction regardless of the date.
To set up the validation rule, you specify that the extracted time from your date and time field must be greater than or equal to 9:00 AM and less than or equal to 5:00 PM. Be aware that when entering this into Access, sometimes the system will try to add extra quotes or modify your expression if brackets or spaces are involved. Even without spaces, you may still need brackets around your field name. It is important to check this carefully, as incorrect syntax can cause your validation rule to fail.
Once the rule is in place, you can test various entries. If you try entering an appointment for noon, it goes through just fine. Try one at 10 p.m., and Access will block it because it is outside the permitted range. Initially, Access displays a generic error message, often referencing December 30, 1899, which could confuse your users. To fix that, set a Validation Text message letting users know, for instance, that "Appointments must be between 9 a.m. and 5 p.m. Thank you for your cooperation." This creates a clear and user-friendly experience.
I have many more lessons on working with date and time functions in Access. My Access Expert 27 and 28 classes go in depth on these topics, covering every function available and including additional examples such as calculating ages, filtering for upcoming birthdays, and more. If you want to explore this further, be sure to check out my Access Date Time Seminar. In it, I also show how to calculate business days between two dates, similar to the NETWORKDAYS function in Excel, including how to take holidays into account, even those with rules like "the first Monday in September."
That covers the Time Value function and its use with validation rules for appointments. For a complete video tutorial with step-by-step instructions on everything discussed here, visit my website at the link below.
Live long and prosper, my friends.Topic List TimeValue function overview in Access
Extracting time from a date/time field
Creating a query to separate date and time
Using DateValue and TimeValue in queries
Setting up an appointment table with date/time fields
Understanding reserved words for field names
Differences between storing date and time together or separately
Setting up a validation rule for time entry
Configuring validation rules for time ranges (9am to 5pm)
Using TimeValue function in a validation rule
Troubleshooting field name issues with brackets in Access
Adding custom validation text for user-friendly error messages
Testing validation rules for valid and invalid appointment times |
