Using DateSerial Function in Access

Dates are popular criteria in queries.  Date ranges ("between #1/1/2015# and #1/31/2015#", for instance)  are useful when you want to see everything that happened in January of 2015.  But if you want to see what happened last month, you have to open the query and type in the range, or -- if you're using fields on a form to supply your query with criteria -- enter the dates before you run the query.  If you regularly run "LastMonthsSalesReport", or "ListAppointmentsNextMonth", changing the date range can get old real fast, and -- if you don't remember that February has 28 days except in leap year -- can be error prone.

Here's a way to use the DateSerial function to set date range criteria for last year or next month.

DateSerial accepts integers and using those integers returns a Date.  You can use literal values (DateSerial (2015, 3, 1) ) to return the first day of March, 2015, but using expressions to hand DateSerial integers is where the real power is. This article was written on March 20, 2015. Today, pasting ?Year(Date) into the Immediate pane and hitting  returns an integer -- 2015 -- that is the current year. The same kind of formatting can return integers for the current month and current day-of-the-month: ?Month(Date) returns 3; ?Day(Date) returns 20.

Pasting ?DateSerial( Year(date), Month(Date), 1) into the Immediate pane and hitting  returns the date of the first day of the current month. The returned value of that expression will change depending on the day it is run: on March 20,  2015, it will return 3/1/2015 ... but on April 3, 2015, it will return 4/1/2015. When addition or subtraction are used with literal values to modify the expressions, you can define dates in the past or the future by their relation to the current date. ?DateSerial( Year(date), Month(Date) -1, 1)  returns the first day of the month before the current one. ?DateSerial( Year(date)-1, 1, 1)  returns the first day of the year before the current one. Here are some of the DateSerial expressions I use most frequently as criteria in my queries:

Last Month:  Between DateSerial(Year(Date), Month(Date) - 1, 1) and DateSerial(Year(Date), Month(Date), 1) - 1
Last Year:  Between DateSerial(Year(Date) - 1, 1, 1) and DateSerial(Year(Date) - 1, 12, 31)
This Month:  Between DateSerial(Year(Date), Month(Date), 1) and DateSerial(Year(Date), Month(Date) + 1, 1) - 1
This Year:  Between DateSerial(Year(Date) , 1, 1)  and DateSerial(Year(Date), 12, 31)
Next Month:  Between DateSerial(Year(Date), Month(Date), 1) and DateSerial(Year(Date), Month(Date) + 1, 1) - 1
Next Year:  Between DateSerial(Year(Date)+1 , 1, 1)  and DateSerial(Year(Date)+ 1, 12, 31)

DateSerial can be used in text boxes that are report titles, too:
="Sales Between " & DateSerial(Year(Date) - 1, 1, 1) & " and " & DateSerial(Year(Date) - 1, 12, 31) will display on the report as "Sales Between 1/1/2014 and 12/31/2014"  (if the report is run the same year this article was written). If you like your titles nicely formatted, use ="Sales Between " & Format(DateSerial(Year(Date) - 1, 1, 1), "MMMM, D, YYYY") & " and " & Format(DateSerial(Year(Date) - 1, 12, 31), "MMMM, D, YYYY") in your report's title text box, and you'll have "Sales Between January 1, 2014 and December 31, 2014" as your report title.

Thanks for reading my article;  please leave feedback if you have questions or comments.  If you found it useful, please click the green "Vote this article as helpful" button below.  I look forward to hearing from you.

Paul Cook-Giles
www.LinkedIn.com/in/PaulCookGiles

Additional Information:  
The "Immediate" Window:  http://www.learnaccessnow.com/chap20d.html
DateSerial:  https://support.office.com/en-US/Article/DateSerial-Function-a0128476-83a0-407c-831a-93f2b046f503?ui=en-US&rs=en-US&ad=US
Dates as Criteria:  https://support.office.com/en-us/article/Examples-of-query-criteria-3197228c-8684-4552-ac03-
aba746fb29d8#bm4