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.