The DateSerial function is the most powerful method to calculate dates that Access offers. It takes three arguments:

Year
Month
Day

Now at first glance you may well be saying “so what – all dates have a year, month and day?” DateSerial is powerful because you can manipulate these elements individually or together in whatever combination meets your needs.

The best way to understand the power of this function is through examples.

Let’s say our goal is to determine the last day of March 2007. We did this last month using the DateAdd function and it was reasonably complex:

DateAdd("m", 1, #3/17/07#) - Day(DateAdd ("m", 1, #3/17/07#)) +1

As you can see, using DateAdd makes it possible to do the calculation, but it’s a complex two-step process. With DateSerial, it’s a one-step process:

DateSerial(Year(#3/17/2007#), Month (#3/17/2007#) + 1, 1) – 1

We’re using March 17, 2007, but you could easily use any date that you have in your system: a due date, a birthday, whatever you may have.

The first variable, Year Year(#3/17/07#) is straightforward. We’re extracting the year of from our date; in this case, our function will return 2007.

The next variable, Month, is tougher to explain. Our overarching goal is to find the last day of March 2007, but our short-term goal is to get to the first day of the following month. Why the following month? Well if we can get to the first day of April and subtract one day, we’re then at the last day of March. This approach completely eliminates the need to determine how many days are in a specific month.

So to determine the first of the month following our date of interest (3/17/07), we take the date (#3/17/07#) and add 1 to the date function: Month(#3/17/07#)+1. The placement of the parenthesis is critical. Since we want the following month, we must enclose the date within parenthesis prior to adding the 1. If we don’t enclose our date in parenthesis, we run the risk of adding 1 to the date, which usually leaves you in the same month rather than getting you to the next month. So now the month function will bring us to April, the month immediately following March.

The last variable is Day, and we enter the value directly as 1 (note that it is not preceded by Day) since we want the first day of the month.

Using DateSerial, we’ve calculated our way to April 1. That final -1, added outside the parentheses enclosing the DateSerial function,

DateSerial(Year(date),(Month (date())+1,1)-1

backs us up one day to give us what we want: March 31, 2007.

Alternately we could get to the same point with

DateSerial(Year(#3/17/2007#), Month (#3/17/2007#) + 1, 0)

The difference here is that rather than get to April 1 and subtract 1, we use a zero for the day, which Access interprets as the day prior to the first. This works, but personally I find it less intuitive and when you’re building a complex application you want to be able to debug it in the future. The cleaner your code and logic, the easier debugging will be.

Let’s step through the process to determine the date three months before your 65th birthday using the DateSerial function. Intuitively, you’d take your birthdate and add 65 years. Then take that date and subtract 3 months. Using DateSerial we can do this in one step.

Let’s say you’re doing this particular calculation in a query (it could also be a report, a form, or in code) and that you have a field representing Date of Birth called “DOB”. The formula would be:

DateSerial(Year([DOB]) + 65, Month([DOB]) - 3, Day([DOB]))

As in our prior example, we use the three arguments: Year, Month and Day. For our first Argument [Year], we use Year(DOB) to calculate the year of the date of birth and then we add 65. Our second argument [Month] is very similar: Month ([DOB]) – 3. We use the month function to determine the month of the birthday and then subtract 3. The day of the month is even more straight-forward; we use the day function to calculate the day of the month of the birthday. So if you have a record in the DOB file with a date of April 26, 1951, you would enter our formula of

DateSerial(Year([DOB]) + 65, Month([DOB]) - 3, Day([DOB]))

And you would get back January 17, 2072.

Just kidding!

This month we saw the power of the DateSerial function. Using this approach there is virtually no date math that can’t be calculated. For the simple things where you need to simply add or subtract a set period, be it day, year, or month, the DateAdd function will do the trick. But when things get complex, break out the big gun of date math: DateSerial

If you do what comes naturally and you need to put a date into a query, you’re probably inclined to enter something on the order of 4/15/07. If you do this, you’ll get a nonsense result since Access will do the math and will interpret the date as 4 divided by 15 divided by 7. What you need to do is enclose the date in the pound symbol “#” as in #4/15/07#. By doing this, Access will recognize this as a date, not a calculation, and return a value based on what you enter.