If you try to calculate someone's age, you may be tempted to use the datediff function. This is a handy tool that allows you to calculate the difference between any two dates. Although I haven't talked about this function in any great depth in the past, I've referred to it periodically. In a future Wizard, I'll dig into it more deeply.

The common mistake is to believe that you can calculate a person's age just by getting difference between the year of their birth and the current year.

While this might seem like reasonable logic, it runs into one of those many date math problems. It's important to know that the datediff function completely ignores months and days. To demonstrate this, I've constructed a query below that shows what you might be tempted to do. Note that I'm using the DOB or date of birth field as one of my inputs, and the function "now" as the second input for calculating dates.

When you run this function you get the following:

I ran this query on and March 15, 2009. As you can see for Billy Millie on March 15, 2009, it correctly reports that he is one year old since he just had a birthday on the day I ran this. However Jim Nasium is also reported as being one year old, even though his birthday is on the day after the run date.

Again, the problem is that the query completely ignores months and days; it just does a calculation of the difference between years. In many cases, this is okay. For example, if you are just interested in an elderly population of about 60 years old or older, then the datediff function works just fine because you're not really interested in having a precise demarcation line. But many times, getting the correct age is critical.

To calculate age accurately, we can still use the datediff function, but we have to adjust. If you replace the straight datediff syntax I used earlier with the following, you'll get the result you looking for.

DateDiff("yyyy",[DOB],Now())
+Int(Format(Now(),"mmdd")
< Format([DOB],"mmdd"))

If you're copying and pasting this make sure you eliminate line wraps.

Let's pull this apart. The first section DateDiff("yyyy", [DOB],Now()) is the same as before. However, after this point we get into some tricky bits. Our goal is avoid counting a year if the person has not yet had a birthday.

The heart of this calculation takes place with the following:

+Int(Format(Now(),"mmdd")

This month, we took look at how to accurately calculate a person's age in years. This is not the only approach to use. You could certainly have used VBA code, which would have been much easier to follow, especially if you were interested in breaking it into something that had more digestible bits. Nor is this logic the only approach that could have been used to get the correct age into a query; it's just one that works.

I invite you to play around with this approach and develop others to meet your needs. The same type of tricks can be used to calculate time into the future or even to calculate by months instead of years.