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

Calculating Age - It's Not As Easy As You'd Think

I was recently at my brother's 60th birthday. At this family function, there were people who were well into their 80s, and there was a baby who was only 6 months old. It's amazing to think that one of my siblings is 60 years old. What does that say about me?! Unfortunately or perhaps fortunately I, like everyone else in the world, am aging.

Occasionally when working in Access databases, you have a need to know how old someone is. Calculating someone's age in years is not nearly as simple as you might initially think. This month, we'll show you a way to answer the question: How old is someone today?

In this Issue
  • Tip of the Month - Two Access Apps at the Same Time
  • Calculating Years with DateDiff
  • Calculating Age the Right Way
  • Conclusion

  • Calculating Years with DateDiff

    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.


    Calculating Age the Right Way

    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")

    Notice that that we have a format function that calls on a date and formats it as Months and Days: Format (Now(),"mmdd". This happens twice; once with the now function and once with our DOB field. It then compares them with the < symbol.

    The second tricky bit is the use of the less than "<" symbol. We're comparing the two dates and saying that the current month and day is a less than the month and day of the birthday. One of two answers is possible, either today is earlier in the year than the birthday, or it is not. It returns the answer as a True or False.

    If something is true, Access stores this as a minus one. If it is false, Access will interpret it as a zero. So if the month and day is currently less than the month and day of person's birthday, then we get a negative 1. Otherwise, we get a 0, which we then add back to the number of years with the "+Int" command. The Int serves to ensure that a whole number (an integer for the math fans) is returned.

    To be more succinct, the function as a whole takes the difference in years and, if the person has not had a birthday yet in the current year, it reduces the calculation by one.

    Let's see, what happens in the query when these two different functions are run next to each other.

    Unless you're a developer or doing tricky date math, it's not really critical that you understand each of the elements of this approach. If you are a casual user, feel free to just cut and paste the function and use it whenever you need it. Of course, you'll want to validate that it works the way you expect it will.


    Conclusion

    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.


    Tip of the Month - Two Access Apps at the Same Time

    When you're working with Word or Excel and you need a new document or workbook, you go to the file menu and choose new. Your current document or workbook is still open in addition to the new one. If you do the same thing with Access, your current program closes and a new database opens.

    This occurs because Access takes up a lot of your computer's memory and the default behavior closes your current program before opening a new Access Application.

    Access tends to use up a lot of memory (RAM in geek- speak), so Microsoft built this behavior to help with memory management.

    Frequently, though, you'll want to have two Access databases open at the same time. To do this, rather then use the menu at the top be a screen, go to your start button, and then navigate to whatever routine you use to normally start Access. With this approach, you'll find your first program stays open and you will also have your new one available as well.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services



    Join our mailing list!

    Forward email

    Safe Unsubscribe
    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