On my Contextures website, the focus in on Excel tips and tutorials. Behind the scenes, I use Microsoft Access too, for time tracking, website statistics and a few other key tasks. This week, I wanted to find files that hadn’t been updated for a long time, so I created a query that selects records older than X – Access asks what X is each time the query runs. Here’s what I built, and a few other Access query date criteria examples.
Access Query With Simple Date Criteria
If there is a date field in a table, it’s easy to build a simple query that selects record before or after a specific date. For example, use this criterion in the date field, to select records that were revised on or before January 1, 2017.
<= 1/1/2017
Access automatically puts number signs before and after the date, when you press Enter, or click away from the criteria cell
<= #1/1/2017#
Access Query Criteria for Specific Date Range
To select records within a specific date range, with two dates with the AND operator, in the query criteria. Here’s the criteria string that selects records from January 1, 2017 to May 31, 2017 (including those dates).
Between #1/1/2017# And #5/31/2017#
Query With Changeable Dates
Just like an Excel date formula, it’s usually better to have a variable in an Access query, instead of hard coding a specific date. In Excel, a formula could refer to a worksheet cell, and a date could be entered there, and changed easily.
In Access, you could have a text box on a form, and enter a date there. Then, refer to that text box in the Access query date criteria.
- Tip: See another Access form with date range boxes, and get a small sample file that shows how the Start Date and End Date boxes work.
Here is a form where I can enter the start and end dates, and then run a series of reports based on those dates. It’s easy to change the dates before running the reports, and very efficient for doing month end summaries.
In the queries that the reports are based on, the date criteria refer to those text boxes.
Between [forms]![Menu].[txtStart] And [forms]![Menu].[txtEnd]
Query With Empty Date Boxes
[Update] In the comments, someone asked how to show all the data, if the date text boxes are left empty.
To do that, add 2 more fields in your query, with the text box names in them.
- [forms]![Menu].[txtStart]
- [forms]![Menu].[txtEnd]
Access automatically adds a name for each field – Expr1 and Expr2
Then, in the 2nd Criteria Row (OR), type Is Null in each of the new columns.
- Don’t put the new criteria in the same row as the existing criteria, or you won’t get any results when you run the query.
Query With Prompt for Dates
If you’re not running a series of reports from an Access form, it’s easier to use parameters in your query criteria. The parameters will prompt you to enter the start and end dates, or other criteria that you want to have as variables.
So, to select old records with a query, I can put a parameter in my original query, replacing the date. The Parameter is enclosed in square brackets.
<= [Before what date?]
When the query runs, the parameter will appear in a pop up message. Type a date in the input box, and click OK, to see the results.
More Access Query Date Criteria Examples
If you don’t want to enter the entire date (that can get tiring!), use a parameter as part of a date. In the next example, the DateSerial function is used in the criteria, and the parameter prompts you for the year.
The month (1) and day (1) are hard coded – the query will select everything before January 1st of the year that you enter.
<DateSerial([Before what year?],1,1)
How Many Years Old?
Maybe you would rather focus on the record age, instead of a specific year. In this example, I used the DateAdd function, with year as the interval, then a minus sign, and a prompt for how old the files should be. The Date function at the end will base the calculation on the current date.
<=DateAdd(“yyyy”,-[How many years old?],Date())
When you run the query, enter a number in the input box, and the query will select records that are on or before that date – X years before the current date.
__________________
Interesting, I have a variant on this that I’m stuck on. I run a whole range of queries that start from the previous 1 April. At present my queries run from 1 April 2018, but shortly I will want to change all the queries to 1 April 2019. What’s the simplest way to store a start for reporting year date – 1 April 20nn?
How would a parameter query be structured that had Start and End parameters asking the user for those…but, the user wants *all* dates instead of a range?
Thanks!
I have this exact same question! Did you find an answer?
Thanks, Michael, and I’ve added a new section with instructions for showing all the dates.
Debra, thanks…and, I received your email. But, I can’t seem to make it work on my end. Tried to email you back with screen shots, but it bounced. Suggestions?
Dear Debra,
Thank you for the post on you website. I have a data with transaction date, from the database i want extract transactions of Sundays only. would help me how to do in a query format?
Thank you
Great article! Thanks
Wondering how to get 14 days of data returned prior to any date entered into the query? Thanks
Can we actually ask for the user to enter only the name of the month in the parameter? how to write the criteria in the query? cos i need to generate monthly report.
The section on “query with empty date” really solved my problem.
Thanks
I have 2 fields in a table. Cal Date and Expiry Date. If I want to add 2 year minus 1 day to the expiry date field from the Cal Date. how would I do that?
dateadd(“d”,-1,dateadd(“yyyy”,2,now))
Thanks, Dick – I missed that question!
How can I do something down this line but add to the date? For example, I have DueDate but on the criteria i would like to add a formula where it pulls DueDate and it adds 15 days to that due date. Is this possible?
Hello! I am trying to see all records estimated to deliver prior to a date six weeks from today. Currently I am using Expr1: DateDiff(“m”,Now(),[Estimated Delivery]) with criteria 0 or 1. Then I am exporting to Excel and deleting the lines that exceed six weeks. I’d love to be able to have Access do this so I can just send the report one button and done. I’d appreciate any advice! Thanks
Thanks, Jennifer, and you could use this criteria in the Estimated Delivery column of your query design:
Between Date() And (Date()+7*6)
I am attempting to get results less than 7 days after current date and 30 days after current date. How would that look in design view?
Hello, I have added the Is Null criteria to show all dates if the date fields are empty but this also removes another criteria and shows all Locations. I have a location combo box and then two dates, I still want it to filter by location when dates are empty. Any suggestions?
Add the location criteria again, on the row where the Null date criteria is entered
Hi Debra,
On an inherited database, I am requiring a query (Access 2007) to prompt to enter a parameter (year) from the format dd mmm yyyy, for example, 08 Aug 1846. For printout reasons I do not wish to have the field in format yyyy mmm dd.
On the query I use LIKE to extract the surname and forename, which work perfectly, and wish to also search on an input year on the same query.
Hope the above makes sense and you can help with a suitable query format.
Best wishes,
Jeff
I have a Family DB that lists B’Days. I have a query to search for birthdays in the current month to remind me to call that person to wish them a happy birthday. I have a query for birthday in the next month. This allows me to send a B’Day card early enough so they will get it in time. The problem is when the month is Dec my Next month query does not return any birthdays for Jan. There are 8 birthdays in Jan. In the criteria line of query that works from Jan to Nov I have Month(Now())+1. But in Dec it will not return Jan B’days. Can you point me in the right direction?
You can check if the month number is 12, and use 1 in that case:
IIf(Month(Date())=12,1,Month(Date())+1)