The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
February 2007

Date Math 102 – The DateAdd Function

What's the easiest way to get to a date in the future or in the past? If today is February 12 and you need to know the date 2 weeks from today, you’d add 14 (2 weeks) to 12 and get 26, so two weeks from February 12 is February 26.

Now let’s imagine that you need to subtract two weeks from February 12. Well 12 minus 14 = -2. Not so simple now, is it? And what happens if you have to determine the date 3 weeks from February 12? If you add 12 + 21, you get 33, which takes you into March. Now how many days does February have? Is this year a leap year? Hmm – even more complex.

This month I'll review a built-in Access function called DateAdd which greatly simplifies these complex date math problems.

In this Issue
  • Gratuitous Date Jokes
  • DateAdd – The Basics
  • Arcane Corners of DateAdd
  • Date Add Input Alternatives
  • Conclusion and a look ahead
  • Trap of the Month – Be careful when entering the Year

  • DateAdd – The Basics

    The DateAdd function takes three arguments: interval, number, and date.

    Interval refers to the period, or units, that you want to add or subtract. The most commonly used are:

    Year – yyyy
    Month – m
    Day – d
    Week – ww

    For a full list of possibilities, search DateAdd in Access Help.

    Number refers to the number of units you want to add or subtract from the date that you’re interested in. If you want to subtract, use a negative number.

    Date refers to your reference date.

    Here is an example showing how we could use the DateAdd function to accomplish one of the calculations mentioned earlier.

    To add 14 days from February 12 we'd use the DateAdd function and the format DateAdd(interval, number, date):

    DateAdd("d", 14, #2/12/2007#)

    To try this yourself, open a code window (Alt-F11) and type
    ? DateAdd("d", 14, #2/12/2007#)
    in the immediate window. You will see the output to this function is 2/27/2007.

    There are a couple of important things to note. First, the interval must be surrounded by quotes. The second is that if you are supplying what could be interpreted as a number you must bracket the date with a date indicator symbol, typically the "#" or pound symbol. This tells Access that the characters within the pound sign should be interpreted as a date; otherwise it will interpret the forward slash as a division symbol and do the math to determine the date. In our case it would divide 2 by 12 and then divide that number by 2007. The starting point would then be calculated as Dec 30, 1899.

    Arcane Corners of DateAdd

    You can use this function for more than just date math; you can also use it for time math. Additionally you could also use this function for both date and time math simultaneously.

    If you've ever tried to add 37 minutes to a time, you already know how complex this can be. The syntax is the same, however you must pass in a time, and use h, m, and s for hours, minutes and seconds to designate the time.

    For instance if you wanted to add 3 hours to the time to see what time it is in California you’d use DateAdd("h", 3, Now()). If you had a specific time that you wanted to add or subtract to you put the time in double quotes, like DateAdd("h", 3, "10:44AM"). The time format will take a variety of inputs, so you’ll want to experiment to see which one works best for you.

    Use caution when doing time math: if there's also a date involved, once you pass midnight the date will move forward one day. This may be what you'd expect, but if you aren’t paying attention, it might catch you by surprise and trip you up.

    Date Add Input Alternatives

    There are a couple of alternate methods of representing the date in the DateAdd function. In addition to #2/12/2007#, you could also type "Feb 12, 2007" surrounded by quotes. Or you could surround the "2/12/2007" with quotes and that will work as well.

    I tend to use the pound symbol because that convention can be used in queries. In addition, quotes are frequently used in Access code (e.g. in constructing strings), so fewer quotes makes for cleaner code, in my opinion.

    Conclusion and a look ahead

    This month we've tackled the easy part of date math. More complex issues, to be addressed with more powerful tools in a future issue, include questions like “what is last day of next month?” or “what date is one month before cousin Max’s third birthday?”

    Trap of the Month – Be careful when entering the Year

    If you're dealing with “current” dates, you may omit the century. Entering the year as 2007 and 07 will get the same result. If you enter 99, Access will think you're talking about 1999. If you enter 10 for the year Access will think that you're talking about 2010. You have to be careful here, because if you enter 30 as the year, Access will think you mean 1930, when you may intend 2030.

    As a general rule, your best bet is to use 4 digits for the year. Different versions of Access interpret two digit years differently. In some cases, Access will interpret things in an unexpected manner, especially during years with a February 29, which have a surprising level of complexity.

    Gratuitous Date Jokes

    Have you lived here all your life? Not yet.

    How many birthdays has a 97 year old man had so far? One

    A boy is about to go on his first date, and is nervous about what to talk about. He asks his father for advice.

    The father replies: "My son, there are three subjects that always work. These are food, family, and philosophy."

    The boy picks up his date and they go to a soda fountain. Ice cream sodas in front of them, they stare at each other for a long time, as the boy's nervousness builds.

    He remembers his father's advice, and chooses the first topic.

    He asks the girl: "Do you like spinach?" She says "No," and the silence returns.

    After a few more uncomfortable minutes, the boy thinks of his father's suggestion and turns to the second item on the list. He asks, "Do you have a brother?" Again, the girl says "No" and there is silence once again.

    The boy then plays his last card. He thinks of his father's advice and asks the girl the following question: "If you had a brother, would he like spinach?"

    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