Date Criteria
By Richard Rost
4 years ago
Properly Using Date Criteria in Microsoft Access
One of the most frequently asked questions I get is, "how do I create a query to get records between two dates." There are a lot of different methods you can use. Whichever one is best depends on the situation and your level of Access knowledge. I've got a ton of other videos on this subject, and I'll give you some links to them at the end, but consider this video a summary lesson where I'm going to walk you through many of the different ways you can get records between two dates. Plus, I'm sick of having to remember which of my other videos to recommend to people, so watch this one and then you can choose yourself which additional videos to watch. OK, here we go.
Recommended Course
Links
Topics Covered
- Before or after a specific date
- Before or after today's date
- Date() and Now() functions
- Within the last 30 days
- In the next 30 days
- Between two dates (be care with times)
- Parameter queries
- Getting criteria from an open form
 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, inequality, Examples of using dates as criteria in Access queries, How do I get data between two dates in Access, How do you use between in Access, How do you set a date criteria in Access, How do I create a date range in an Access, How to select records between two dates in Access, Access database query date parameters, ms-access query between two dates, using dates as criteria in access queries, access query date greater than 30 days, Create a query that accepts Start date and end date, using dates as criteria in Access queries
Subscribe to Date Criteria
Get notifications when this page is updated
Intro In this video, we will talk about different methods for setting date criteria in Microsoft Access queries. You will see how to filter records before, after, or between specific dates, use built-in date functions like Date and Now, work with parameter queries that prompt the user for input, and set up forms to provide date ranges dynamically. I will also explain important tips to avoid common problems with date and time values in your tables. This collection of techniques will help you choose the best way to filter your data by date in your Access databases.Transcript Welcome to another FastTips video brought to you by AccessLearningZone.com. I am your instructor Richard Rost. In today's video, we are going to talk about different methods for creating queries with date criteria in your Microsoft Access databases.
One of the most commonly asked questions I get is how do I create a query to get records between two dates. There are a lot of different methods you can use; whichever one you choose best depends on the situation and your level of Access knowledge. I have a ton of other videos on this subject, and I will give you some links to them at the end of this video, but consider this a summary lesson where I am going to walk you through many of the different ways you can get records between two dates, or after a certain date, or before a certain date.
I am also tired of having to remember which of my other videos to recommend to people. So you can just watch this one, see all the different techniques, and then you can pick the other videos that you want to watch on your own.
Here we go.
Here is my TechHelp free template. This is a free database you can grab a copy of from my website if you want to. In here, I have an order table, very simple. I have an order ID, an order date, and a description. Of course, there are other fields over here, customer ID, and its pay, which we do not need for this lesson. All we really care about is the order date.
Today, it is currently July 1st, 2022. Keep that in mind as I am talking about things like the last 30 days or between 30 days ago.
Generally, if you are going to be getting records between two dates, you are going to want to display them in a form or a report. If that is the case, you are almost always going to use a query to limit the records that you want to see, and then you will base your form or report off of that query. For example, if you want to do a report showing all of your orders in the last 30 days, you will make a query to do that first, then you will build the report off of that query.
The first thing people always want to see is how do I show all the records before or after a specific date. Let's go create query design. Bring in my order table - that is all I need for now. Here are all the fields right here. I am going to bring in the fields I want to see on this query. Let's bring in order ID, order date, and description.
If you do not know how to make a query or a table, go watch my Access Beginner 1 class. It is absolutely free; you will find that on my website and on my YouTube channel.
Down here, we can put our query criteria in to limit what we want to see for each of these fields. For our order date, we can put in that criteria right there. Once again, if you do not know how to use query criteria, go watch this video. You will find links to all these in the description text below the video.
If you want to see all the orders after a specific date, let's say June 1st, we are going to come down here in the criteria box under that field and type in greater than 6/1 like that, press tab, and then Access will convert that to a date for you. I will zoom in so you can see it. Notice that Access puts dates inside of those pound signs, those hashtags.
I am using the ISO date standard format, so it is internationally known, and it is year, month, day. If you want to learn more about that, there is a video.
When I run my query, I will see only orders after that date. Then I sorted it all here. Let's go back in here and just sort that real quick, sort, drop it down, and then run it. There we go. There are all the orders after that specific date.
If you want to see all the orders before a specific date, you change that to less than. There you go. If you want to include that date, like less than or equal to, just put less than and then the equal sign right next to it and then run it. There you go. There is 6-1 right there.
Now, there are two functions to know when you are dealing with dates: Date and Now. Those are reserved words. The Date function returns the current date as of your system clock right now. For me, it is July 1st, 2022. The Now function returns the current date and time.
If you want to see all of the orders before right now, you would go less than Now and then open and close parentheses, just like that. I will zoom in again so you can see it. Less than Now. That means give me all the orders before right now. If I run this, I do not think I have any future orders in here. Let's put some in.
Back to the order table, and I will put a couple of future orders in here. Let's put one in here from 7-8 and call that 'future 1', and I will put one in here from 8-7 and call that 'future 2'. Those two orders should not show up when I run this query now because they are in the future.
Ready and run it. See? They are not on there. That will update itself automatically every time you run that query.
There is Now, and then there is Date, which is the same thing but just Date. It is today's date at midnight. Be careful because if you have times in your table, you have to take those into consideration. Go watch this video for more on that. This one actually focuses more on the Between key, which I am going to mention in a second, and how you have to be careful if you have times in your tables.
For example, let me show you here: if I go back to my order table and today is the first, if you had your order table storing the current date and time, for example, let's say it puts 6 p.m. in there for the Klingon Toothstrapener, and if I come back here now and I say I want to see all of the orders less than or equal to today's date and I run that, I am not seeing the Klingon Toothstrapener because less than or equal to today's date is today's date at midnight. Be very careful about that. Watch that other video for more information on that one.
If you do want to see orders that are in the future, you can just say greater than or equal to today's date. There you go. There is your Klingon Toothstrapener, our future orders, and Odo's bucket because it was today at midnight.
Now what if you want to see all orders that are older than a month, so less than 30 days ago? In order to do that, you have to understand that in Access, a date unit is equal to one day. If I say Date +1, that is tomorrow. If I say Date -1, that is yesterday. Date -30, for example, will be 30 days ago. I have a whole separate video that explains this date math in more detail.
If I want to see orders that are at least 30 days old, I can say less than Date -30. If I run that now, you will see nothing in there is newer than 30 days ago, which would be June 2nd, I think. The same thing would apply to the future. If you are doing appointments, for example, and you want to see all your appointments for the next seven days, you could say less than Date +7.
What if you want to see orders between two dates? There is the Between keyword. Again, be careful with it. You can say Between, let's say 6/1 and 7/1. Notice that Access converts that over to my date format. When I run that, there are all the orders between 6/1 and 7/1. But notice also the Klingon Toothstrapener is not on there because that is 7/1 at midnight. Be aware of that.
You could also say, instead of Between, you can say greater than or equal to that value and less than or equal to that value, or any combination thereof. Same thing. When I run it, there you go. If you have times here and you want to make sure you include all those, you can say less than 7/2 because less than 7/2 will include orders on 7/1 with a time like 7/1 at 6 p.m. but it will not include 7/2 at midnight because we want less than that. If I run this now, there you go. There is your Klingon Toothstrapener.
Now, what if you want the user to type in those dates when the query runs? We are going to use a parameter query. Again, here is a video that goes into more depth. A parameter query lets you specify the criteria when the query runs.
You can say greater than or equal to and then put inside of square brackets exactly what you want your prompt to read. I am going to hit Shift+F2 to zoom in so you can see this better. It will be greater than or equal to [Enter start date] and less than or equal to [Enter end date]. There are my two prompts, and now when I run this query, watch what happens. Make this bigger so you can see it.
Run it. See that? Enter parameter value. Enter start date, let's say 6/1. Enter end date - if you do not specify a year, by the way, you get the current year. Enter end date, 7/2, and there we go. You can see my criteria included the Klingon Toothstrapener because I went to 7/2.
That is called a parameter query. Again, I have another video that goes into that in a lot more detail.
If you want to put those prompts on a form instead of having them pop up when the query runs, you can do this. Watch. Let me save this query for now. Let's just call this my OrderQ, that order query.
You can put prompts on a form. I will put them here in my Main Menu. It is called my Main Menu form. I will just take this guy here. This name's current date; let's call this one StartDate. You can put a default value in here if you want to. You can put a control source if you want to make it stick. I am going to go to Data and put a default value in here that is going to be equal to Date - 30. My default value for this one is going to be 30 days ago. StartDate. Let's do the formatting real quick. Format is Auto, that is fine.
Then my EndDate - I just copied and pasted that one. EndDate. Let's give it a name. Come here. EndDate. We will change the default value so it is just equal to Date.
This way you do not have to keep typing those in every time the form loads. I will close this. Then reopen it. See that? It automatically got 30 days ago and today's date as my parameters there.
Now whenever that query runs - my OrderQ - I want it to look at this form and get these values. Here is how we do that. Design view. In the criteria again, I am going to zoom in so you can see this better. We are going to say greater than or equal to Forms!MainMenuF!StartDate and less than or equal to Forms!MainMenuF!EndDate. See that? That is called getting a value from an open form. I have a video for that too - watch for more information on this technique.
Hit OK. Let's save this query (Ctrl+S). Close it. Now when I open up the query, there you go. 6/1 to 7/1, see that? It gets its criteria from that open form.
Those are all the basic ways you can specify date criteria for your queries. I have a ton of different videos available to show you all kinds of different things you can do with dates. This video is just to show you how you can get information between two dates that you specify.
If you want to get a list of birthdays, for example, use the DateSerial function. There is the DatePart function which gives you the different components of a date, like just the year or just the month. There are date functions to do your year-end reporting. There are ways to calculate your sales year to date or month to date. You can calculate the number of work days between two days, like for example, between a Tuesday and the following Monday - how many non-weekend or non-holiday days are there in that range? I could go on and on. I have dozens of hours of videos on just date/time stuff. In fact, my Access Expert Level 27 and 28 classes are my Comprehensive Guide to Access Functions, where we cover just about everything you could possibly know about date and time functions.
Plus, for those of you who are Gold members, you have access to my Code Vault. There are lots and lots of formulas and functions in here for getting all kinds of different ways to calculate different date stuff. For example, I will show you one of them. Here are different date formulas - your day and month, is the date in the current year, is the date in the previous year, is the date next year, is the date in the current month, is the date in this year to date. There are all kinds of different situations like this in the Code Vault. There is all kinds of stuff in here. This is available for Gold members.
I hope that little crash course in date criteria was enough to help you out. I hope you learned a few things. Watch all those other videos. Most of those videos that I just listed are free. They are on my website, they are on my YouTube channel. Access Expert 27 and 28 are available on my website. If you have any questions, feel free to post them down below. We will see you next time.
How do you become a member? Click on the Join button below the video. After you click the Join button, you will see a list of all the different membership levels that are available, each with its own special perks. Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use. You will also get a higher priority if you decide to submit any TechHelp questions to me, and you will get one free Expert class each month after you have finished the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free Developer class each month after you have finished the Expert classes. These are the full length courses found on my website, not just for Access. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you are a sponsor. You will get a shout-out in the video and a link to your website or product in the text below the video and on my website.
Do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more, and they will always be free.Quiz Q1. What is the recommended approach for displaying records between two dates in Access?
A. Use a query to limit the records and base your form or report on that query
B. Filter the records directly on the form after loading all data
C. Use VBA code only to find records between dates
D. Export the table to Excel and filter there
Q2. Which of the following is the correct date format Access recognizes when entering date criteria?
A. MM-DD-YYYY (e.g., 06-01-2022)
B. YYYY/MM/DD (e.g., 2022/06/01)
C. #YYYY-MM-DD# (e.g., #2022-06-01#)
D. DD-MM-YYYY (e.g., 01-06-2022)
Q3. What does the Date function return in Microsoft Access?
A. The current time only
B. The current date as of your system clock
C. The date and time from when the database was last opened
D. The date from the first record in the table
Q4. What is the primary difference between Date and Now functions in Access?
A. Date includes time, Now includes only date
B. Date returns yesterday, Now returns today
C. Date returns the current date, Now returns the current date and time
D. Both always return the same result
Q5. Why should you be cautious when storing time values in your date fields and using criteria like "<= Date"?
A. Because the Date function will not return any records past noon
B. Because "<= Date" only matches times at midnight
C. Because Access cannot compare times to dates
D. Because it may include future records as well
Q6. In Access, what does Date - 30 represent?
A. 30 minutes ago
B. The 30th day of the current month
C. 30 days ago from today
D. Date minus 1 month, regardless of days
Q7. Which of the following methods allows the user to specify dates when the query runs?
A. Using the Between keyword with constant dates
B. Using a parameter query with prompts like [Enter start date]
C. Hardcoding the date values in the SQL view
D. Only VBA code allows for user prompts
Q8. When limiting records between two dates in Access and you want to include all records on the end date (even those with times), which criteria is best?
A. Between [start] and [end]
B. >= [start] and <= [end]
C. >= [start] and < [end + 1]
D. < [start] and > [end]
Q9. How do you reference a value from a text box named StartDate on a form named MainMenuF in a query's criteria?
A. Forms(MainMenuF.StartDate)
B. [MainMenuF].[StartDate]
C. Forms!MainMenuF!StartDate
D. MainMenuF.StartDate
Q10. What Access function can give you only the year, month, or day part of a date field?
A. DatePart
B. DateDiff
C. TimePart
D. YearToDate
Q11. What is a benefit of putting default values in the form controls used for date criteria?
A. It saves the values to your table automatically
B. It ensures prompts appear every time you run the form
C. It pre-fills the controls with likely criteria for the query
D. It disables manual input from the user
Q12. Which of the following is TRUE about Access Learning Zone membership perks?
A. Silver members get access to all Code Vault formulas
B. Gold members can download all sample databases and get Code Vault access
C. Platinum membership is strictly limited to Word or Excel material
D. Only Diamond sponsors receive access to TechHelp videos
Answers: 1-A; 2-C; 3-B; 4-C; 5-B; 6-C; 7-B; 8-C; 9-C; 10-A; 11-C; 12-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 several different techniques for setting date criteria in queries in Microsoft Access databases. One of the questions I get most frequently is how to create a query that returns records between two dates. There are multiple ways to accomplish this, and which method you choose depends on your needs and level of familiarity with Access. I have made many video tutorials about this topic, so think of this lesson as an overview that highlights several ways to pull records from your table that fall between two dates, before a certain date, or after a certain date.
My goal here is to provide you with a clear summary of the available techniques. If you want more details on a specific method, I will mention which videos you can check out for those deep dives, but you can always start with this lesson to understand your options.
For demonstration, I am using my TechHelp free template database. This sample database, available for download on my website, includes a simple order table with fields like order ID, order date, and description. For today's discussion, the focus is on order date. Assume today's date is July 1, 2022, so keep that in mind as we refer to dates like "the last 30 days" or "30 days ago."
Usually, when you want to display records between two dates, the best approach is to use a query. This query will filter your data to just the records that meet your criteria, and then you can build a form or report that uses this query as its data source. For example, if you need a report showing orders from the past month, the first step is to build a query that pulls those orders.
A common starting point is to filter records based on being before or after a specific date. To demonstrate this, I create a query that includes the order table. Then I select the fields I want, such as order ID, order date, and description. If creating queries or tables in Access is new to you, I recommend watching my Access Beginner 1 class, which is free on my website and YouTube channel.
In a query, you can add specific criteria for fields like order date. For example, if you want to see all orders after June 1, you can specify a criteria of greater than June 1. Access will automatically format this as a date when you enter it, and you will notice that it puts pound signs around the date value. I use the ISO date format (year, month, day) for consistency.
Running this query gives you only records after that specific date, and you can sort results as needed. Swapping "greater than" for "less than" lets you see records before a date, and adding "equal to" includes the cutoff date itself.
There are two important built-in functions to be aware of: Date and Now. The Date function gives you the current system date. For me, that's July 1, 2022. The Now function gives you the current date and time combined. If you want to see all records before the current moment, you'd use criteria with Now. If you only have dates stored, Date is sufficient. Be careful if your table includes both dates and times. For example, if you have an order with a date and a 6 p.m. time and try to look for "less than or equal to" today, you might not see it because Date by itself only includes the date at midnight.
If you want to include times, you have to adjust your criteria appropriately or watch one of my videos specifically about working with time values. If you want to retrieve only future orders or those on or after today, just use "greater than or equal to" Date.
Access treats each unit in a date field as one day, so Date +1 is tomorrow, Date -1 is yesterday, and Date -30 is thirty days ago. If you want to see records older than a month, use "less than Date -30." You can also use positive numbers to look at upcoming items, such as appointments in the next week using "less than Date +7."
To find records between two dates, the Between keyword is very useful. You might write criteria like Between June 1 and July 1, and Access will properly format those entries. Be aware, though, that using Between only includes dates up until midnight of the ending date, not the times later on that day. If you want to include all times for your end date, you may need to adjust your criteria by changing the end date to the next day and using "less than" instead of "less than or equal to."
Suppose you want your users to specify the dates when running the query. In this case, parameter queries come into play. By placing prompts in square brackets, you can have Access ask users for the start and end dates each time the query is run. For instance, criteria like "greater than or equal to [Enter start date] and less than or equal to [Enter end date]" will open input boxes when the query runs and filter the results accordingly.
But you can go even further by placing these date prompts on a form. Create StartDate and EndDate text boxes on a form (for example, your Main Menu form), and you can set default values so that each time the form loads, StartDate is Date -30 and EndDate is today's date. The query can then reference these values by specifying criteria like "greater than or equal to Forms!MainMenuF!StartDate and less than or equal to Forms!MainMenuF!EndDate." This setup lets you rerun queries using different dates simply by changing the values on the form.
These are the core methods for filtering records by date in Access queries. There are plenty of variations and more advanced techniques available. For instance, you can use the DateSerial function to find birthdays, or the DatePart function to pull out just the month or year from date values. You can write queries for year-end summaries, calculate sales year-to-date, or even count the number of working days between two dates, excluding weekends and holidays. My Access Expert Level 27 and 28 courses provide a comprehensive look at date and time functions in Access.
If you are a Gold member, you get access to my Code Vault, which contains a wide range of formulas and functions for calculating everything from whether a date falls in the current or previous year to complex year-to-date calculations and more.
I hope this overview of date criteria in Access queries has been helpful and provided you with some new ideas to explore. Most of the videos I referenced here are free and available both on my website and my YouTube channel. The more advanced lessons, including Access Expert 27 and 28, are also on my website.
If you are interested in becoming a member, you can find a Join button on my website. Membership levels include perks like access to extended cut TechHelp videos, free classes, downloadable sample databases, the Code Vault, and priority on your submitted questions. Higher-level members receive even more benefits, including full courses for Access, Word, Excel, Visual Basic, and more.
No matter what, I will continue to make free TechHelp videos as long as you keep watching them.
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 List Creating queries with date criteria in Access
Getting records before or after a specific date
Using ISO date format in Access queries
Applying greater than or less than operators for dates
Sorting query results by date
Using Now and Date functions in queries
Handling date and time together in queries
Understanding date math with Date plus or minus integers
Filtering records older than or newer than a specific interval
Using the Between keyword for date ranges
Combining greater than or equal and less than or equal for ranges
Accounting for times in date criteria
Creating parameter queries for user-inputted dates
Prompting users for date input in queries
Setting up forms to feed date criteria to queries
Using default values for dates on forms
Referencing form fields in query criteria
Running queries based on values from open forms |
