Remove Time
By Richard Rost
12 months ago
Remove Time Part from Date/Time in Microsoft Access
In this Microsoft Access tutorial, I will show you how to automate the removal of the time part from a date-time field using the DateValue function and an update query. Learn how to streamline your Access database by converting imported data, ensuring date-only fields for effective querying and reporting.
Maya from Torrance, California (a Platinum Member) asks: I receive order data in an Excel spreadsheet every day and import it into my Access database. However, all my reports and queries in Access don't work well with the times included in the order dates. I've been manually removing the time part from the dates. Is there a way to automate this process in Access?
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, DateValue function, remove time from date, automating date conversion, update query example, strip time portion, DateValue Access query, update field in Access, order date processing, convert text to date, date time field issues, TechHelp template usage
Subscribe to Remove Time
Get notifications when this page is updated
Transcript
Today we're going to see how to chop off, remove, strip, whatever you want to call it. We're going to take the time portion off of a date-time field and then save that value in the table. Today's question comes from Maya in Torrance, California, one of my platinum members. Maya says, "I receive order data in an Excel spreadsheet every day and import it into my Access database. However, all my reports and queries in Access don't work well with the times included in the order dates." Yeah, that's a pain. I've been manually removing the time from the dates. Is there a way to automate this process in Access?
Yes, Maya, that's one of the things I love about Access - its ability to very easily automate stuff like this. And if I can save you five minutes a day from not having to manually remove times from your order dates, ah, you're going to love this solution, all right?
First, let's talk about some prerequisite videos. I know you know how to import data, Maya, but for anybody else watching, if you don't know how to import data from Excel into Access, go watch this video first. To chop off that time portion, we're going to use a function called DateValue. I've got a separate fast tip video on the DateValue function you can go watch if you want to, but I'm going to show you how it works in just a minute. And to change that value in your table so you get rid of the time and replace it with just a date, we're going to use an update query. So, if you're not familiar with how these work, go watch this video. These are all free videos. They're on my YouTube channel. They're on my website. Go watch them and then come on back.
All right, so let's say I've got a sample orders file that looks like this. I get this every day from the home office, and I've got to import this data into Access to do some stuff with it. And like Maya says, my queries and stuff don't work well because if you've got all your queries that are set to, like, you know, between two dates, if you're not taking that time portion into consideration, it can be tough. I've got whole separate videos on dealing with that and problems with the BETWEEN keyword, and it's a nightmare. So, if you don't need that time portion, it's very easy to strip it off.
I'm going to start by opening up a copy of my TechHelp free template. This is a free database you can grab off my website if you want to, but you don't need to. This will work with any database. Now I'm going to first import that data. So, we're going to go to external data and then new data source from file Excel. We're going to import, we're going to browse to where that file is. The MIS spreadsheet's folder right there under sample orders. Okay, hit open. Hit okay. First row contains column headings. Yeah, that's fine. And we're just going to stick with the defaults as it imports right now. Hit next. All right, we'll say no primary key. We'll work with their primary key. Next, where do you want to put it? I'll put it in import T and then I'll hit finish. And that should import the table right there. There's our data.
Now, it's interesting to note that this actually came in as a text value, but that's okay. For the purposes of our example, that's okay. In fact, you might get import errors if you try to convert this to a date-time field when you import it. It's always easier to do it afterwards. But once you've got it imported, now it's easy to change this and get rid of that time value.
How do we do that? Well, we do that with a query. I'm going to go to create, query design. Now, let's say I just want to bring in the import T. Let's say I want to just look at that order date-time. All right, there it is. Now, if I want to get the date-only portion of it, let's call it date only: all right. I'm going to use the DateValue function. Let me zoom in so you can see this better. Shift F2, zoom in. All right, date only. It's going to be the DateValue function of that order date-time field. All right, let me resize it. It's just a little bit easier. Say it's order date-time. I want date only to be a new field called date only, and it's going to be the DateValue function, which looks at a date that might have a time on it and chops off the time. Okay, and yes, there is also a TimeValue function too, which will take the date-time value and chop off the date. Sometimes that's useful too. We've got a whole separate video on TimeValue.
All right, hit okay. Now, it's still just a select query. We're still just looking at data. So, if I run this now, there you go. There's the value that you want. All right, but I don't want it just over here in a new calculated field. I want to replace this with this. So, for that, we'll use an update query. Okay, so we'll just go back in here. We'll just use the same query and watch this. I'm going to chop this off right here. Okay, chop that off. And then delete the rest of that. I used the cut, Control X. Okay, now we're going to change this query to an update query. All right, I'm going to update this field to that stuff that I just cut. Yeah, that works. I'm loading an import T. I'm taking the order date-time field and I'm changing it to, I'm updating it to the DateValue of order date-time. Okay, and now when I run it, nothing appears to happen because it's an update query. But if I open up import T, now look at this. All those times are gone. We updated that field to just its date value. Okay, and now if you want to, you can save this, you know, update import Q. And now you can run it again in the future if you want to. All right, and if you plan on using this import T for anything important, you might want to come in here and turn this from a short text field to an actual date-time field. Maybe change this to a currency. I usually let Access import that stuff as it is because sometimes you'll get import errors, especially if it's coming from Excel. And one of those date-times isn't perfectly formatted. That happens a lot. So I just bring it all in as short text because then I can see what the problems are here. Okay, some data may be lost when you try to save it because it's converting from text to date-time, but everything's still fine.
And notice now what's the telltale sign that it's now a date field? It lines up on the right side of the column, of the field. Numbers line up on the right, all number-type fields, including currency values, date-times, auto numbers, those are all numeric types. Text types always line up on the left: short text, long text. Okay. So that's how you can do that without any programming.
Now, with just a couple of lines of VBA code, we can automate this process. We can delete the data that's in the table, import a new copy of the data, run that update query, and then say, "Hey, everything's good." And you could do that with just one click and we'll talk about that in part two.
So tune in tomorrow, actually, it's not tomorrow. It's going to be Monday, because today is Friday, December 6, 2024. So we'll be covering this in Monday's video. So tune in Monday, same bat time, same bat channel. Or if you are a member, you can watch it right now, because I'm going to keep recording it in just a few minutes.
But before I let you go, I've got another couple of videos to recommend also. I've got this other one called Date Only. What this does is it will display just the date if there is no time portion. And if there is a time, it will also display the time. This is a cool video. Because if you don't put a time in there, you see all these 12 AMs, 12 AM, 12 AM, 12 AM, if you format it to show the time. This will format it so you won't see it if there is no time. This is pretty cool. And this video will talk about that TimeValue function that I mentioned before that will strip off the date and only show you the time. That's basically the reverse of DateValue.
And if you want to learn more about date and time functions, I've got two classes that cover pretty much everything there is to know about dates and times, Access Expert Level 27 and Level 28. I cover all the different date-time functions: DateAdd, DateDiff, DatePart, DateSerial, ordinals, all kinds of stuff. And I've got this date-time seminar, which is literally just stuff on dates and times. It covers you name it about dates and times. I'm the date-time guy. I love working with dates and times. All right, how to make a holiday table to figure out business days and all kinds of stuff.
All right, so that's going to do it for today. That's how to do the import and the converting it to get rid of the time portion without programming into my Rost class and Monday's class part two. I will show you how to automate this process and I got a couple more little tips for you.
So that is going to be your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you Monday for part two.
TOPICS:
Removing the time portion from a date-time field
Using the DateValue function
Saving a date-only value in the table
Importing data from Excel into Access
Creating and running an update query
Converting text fields to date-time fields
Automating the import and update process with VBA
Using DateValue to update fields without time
Identifying numeric and text field alignment in Access
COMMERCIAL:
In today's video, we're going to learn how to remove the time portion from a date-time field in Access and save it in the table. We'll use the DateValue function and an update query to automate the process, perfect for those of you importing order data from Excel. First, we'll cover importing data into Access and handling texts that look like date-times. Then, we'll create a select query to chop off the time, and update the records. If you're an Access member, stay tuned for part two where we automate this process with VBA. You'll 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 function is used to remove the time portion from a date-time field in Access?
A. DateTimeFormat
B. DateValue
C. TimeStrip
D. DatePart
Q2. Why is it recommended to import date-time values as text initially in Access?
A. It preserves the original formatting from Excel.
B. Date-time fields cannot be imported directly.
C. It avoids import errors due to improperly formatted date-times.
D. Access only accepts text fields for import operations.
Q3. What type of query is used to update a field by removing its time portion?
A. Append query
B. Crosstab query
C. Update query
D. Select query
Q4. In Access, where do numeric and date-time fields align in a table column?
A. Center
B. Left
C. Justified
D. Right
Q5. What additional method is suggested to automate the process of removing time portions from date-time fields?
A. Using a macro
B. Writing a VBA script
C. Manually editing each entry
D. Creating a new table schema
Q6. In the tutorial, what is the benefit of turning a field from short text to a date-time field after importing?
A. It compresses the data to save memory.
B. It prevents future import errors.
C. It aligns the field correctly and allows date-time functions.
D. It changes the field names automatically.
Q7. What other function is mentioned that does the reverse of the DateValue function?
A. DateAdd
B. TimeValue
C. DateDiff
D. OrdinalValue
Q8. What problem do between queries face if time portions are not considered?
A. They generate incorrect ranges.
B. They execute more slowly.
C. They are not compatible with text fields.
D. They create duplicate records.
Q9. What does the tutorial suggest regarding additional resources for learning about date and time functions in Access?
A. Consult Microsoft documentation exclusively.
B. Use forums and user groups for advice.
C. Avoid extra resources as the tutorial is complete.
D. Access Expert Level 27 and Level 28 classes.
Q10. What is a key indication that a field is now a date field in Access?
A. It is highlighted in blue.
B. It contains a calendar icon.
C. It aligns the data on the right side.
D. It has a date picker available.
Answers: 1-B; 2-C; 3-C; 4-D; 5-B; 6-C; 7-B; 8-A; 9-D; 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.
Summary
Today's TechHelp tutorial from Access Learning Zone is focused on removing the time portion from a date-time field and saving just the date in your Access database. Maya has reached out to us because she is having trouble with reports and queries being affected by the inclusion of times in her order dates after importing data from Excel into Access.
Maya, you're in luck because Access is great for automating processes like these. If you can spare five minutes a day from manually stripping times from dates, you'll appreciate this method. Let's walk through the process.
For those unfamiliar with importing data from Excel into Access, it's helpful to review the relevant tutorials before proceeding. To eliminate the time portion of a date, we'll employ the DateValue function. There is a quick tip video on the DateValue function which you can watch if needed, but I'll demonstrate its application shortly. To replace the date-time value in your table with just the date, an update query will be utilized.
Consider a scenario where you have an orders file to import daily, and, as Maya mentioned, your queries struggle because of the time component in the dates. The BETWEEN keyword in queries can lead to problems when the time is involved, which is why removing the time is beneficial.
You can use the TechHelp free template from my website, but any database will work for this example. First, let's import the data by navigating to External Data, choosing a new data source from an Excel file, and proceeding through the import steps. Assume no primary key, and the table will be named import T upon completion.
Interestingly, the imported data might initially appear as a text value, but this is fine for our purposes, and attempting to convert it during import often leads to errors. Post-import, removing the time is straightforward.
By creating a query in the Query Design view, you can isolate the order date-time field. Using the DateValue function assists in obtaining just the date portion. You can label this as "date only" and apply the DateValue function to the order date-time field. There's also a TimeValue function to eliminate the date, which might be useful in certain situations.
Running this select query will display the extracted date in a new calculated field. To modify the existing date-time field with this new date value, convert the select query to an update query. In the update query, adjust the order date-time field to reflect only the date using the DateValue function. After running this query, the times are removed, leaving only dates in the import T table.
For those frequently using the import T table, consider changing its field type from short text to a date-time field or adjust it as needed. Importing as short text initially allows you to identify any errors that might occur due to improper formatting in Excel.
Numeric fields, like dates and times, align to the right side, contrasting with text fields, which align to the left. This distinction can help identify the field types at a glance.
To further streamline this process, a few lines of VBA code can automate the task. By removing existing data, importing a fresh batch, and executing the update query, this entire process can be accomplished with a single click, which I'll discuss in part two of this series.
We will explore this automation in detail in Monday's class. However, for those eager to continue, members can access the next video immediately, as I will be recording it right after this session.
Additionally, I have other helpful videos and classes related to date and time functions in Access—such as Access Expert Level 27 and 28. These explore functions like DateAdd, DateDiff, and others. For anyone interested in mastering date and time manipulation, including business day calculations and holiday tables, my date-time seminar is a comprehensive resource.
This concludes today's tutorial on modifying an imported date-time field in Access. For a complete video tutorial with step-by-step instructions on everything discussed here today, visit my website at the link below.
Live long and prosper, my friends.
Topic List
Removing the time portion from a date-time field
Using the DateValue function
Saving a date-only value in the table
Importing data from Excel into Access
Creating and running an update query
Converting text fields to date-time fields
Automating the import and update process with VBA
Using DateValue to update fields without time
Identifying numeric and text field alignment in Access
|
