$Account.OrganizationName
The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
April 2007

Date Math – DateSerial: The Big Gun

Date math can get really complex. We’ve looked at clever ways to make the DateAdd Function do complicated calculations, but when things get really tough on the calendar front it’s time to break out the Big gun of Date functions – DateSerial!

In this Issue
  • An Invitation
  • Date Serial – The Basics
  • Example 1 - The Last Day of a Month
  • Example 2 - Three Months Before Turning 65
  • Example 3 - How old will you be on your 65th Birthday
  • Conclusion
  • Trap of the Month – Beware of date entry in queries

  • Date Serial – The Basics

    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.


    Example 1 - The Last Day of a Month

    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.


    Example 2 - Three Months Before Turning 65

    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.


    Example 3 - How old will you be on your 65th Birthday

    Just kidding!


    Conclusion

    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


    Trap of the Month – Beware of date entry in queries

    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.


    An Invitation

    Date math is one of the most challenging calculations within a database. If you have a tough date problem to solve (no I can’t help with finding you a date!), feel free to send it my way. I’ll publish the problem and solution to those that are most instructive.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services



    Join our mailing list!

    Forward email

    This email was sent to jim@custom-software.biz, by jim@custom-software.biz
     
     

    Custom Software | Copyright Custom Software All Rights Reserved | Westford | MA | 01886



    __________ NOD32 2188 (20070414) Information __________

    This message was checked by NOD32 antivirus system.
    http://www.eset.com