Age is an important characteristic of many things, including persons. Other things for which age might be important are transactions (in banking and other settings), anniversaries (used in calculating years of service at a company, for instance), and many other places where dates are used. However, it is important to never store an age in a database (I use the term "age" to refer to both the age of a person, as well as of other inanimate or purely conceptual objects such as transactions) because it is a calculated field. As such, storing a calculated field in a database table goes against normalization rules, and should never be done. It changes constantly, so keeping it updated is a constant chore, likely to lead to errors. Instead, the age of anything should be calculated on the fly when needed.
If you are interested, you can find my earlier posts on finding the
median,
the mode,
the geometric and harmonic means,
ranking every row in a query,
selecting random rows out of a table,
calculating running sums and averages,
calculating running differences,
creating histograms,
calculating probability masses out of given data,
calculating cumulative distributions out of given data,
finding percentile scores,
percentile values,
calculating distinct counts,
full outer joins,
parameter queries,
crosstab queries,
working with system objects,
listing table fields,
finding unmatched rows,
calculating statistics with grouping,
job-candidate matching,
job-candidate matching with skill levels,
great circle distances,
great circle initial headings,
using Excel functions in Access,
using the windows file-picker,
using the Access date-picker,
setting tab indexes quickly and correctly,
pre-filling forms based on previous entries,
highlighting form controls,
conditional formatting,
performing date manipulations, and
counting letters, words, sentences and paragraphs.
Given that it is not a good idea to store ages in a database, how do you calculate the age of something on the fly? In this post, I am going to provide the code for one simple way to calculate the age of anything in any unit (years, months, etc.). There are other methods available, and after you grasp the concept, you can enhance the simple method I have provided based on your own requirements.
The basic function used in age calculations is typically the function
DateDiff(). The first argument to DateDiff() determines the units in which the answer is returned. The next two are the dates between which the difference is to be calculated. You can read the help page on DateDiff() to get a better understanding of how it works.
However, there are some peculiarities associated with DateDiff() that you have to understand before you start using it. The problem is that DateDiff() seems to calculate the difference between the given dates based on splitting the dates into pieces and treating them independent of each other.
A few examples might explain this problem a little better. Here are the results from a few DateDiff() calls:
- DateDiff("yyyy", #12/31/2009#, #1/1/2010#) = 1
- DateDiff("yyyy", #1/1/2009#, #12/31/2010#) = 1
- DateDiff("yyyy", #1/1/2010#, #12/31/2010#) = 0
- DateDiff("m", #9/30/2010#, #10/1/2010#) = 1
- DateDiff("m", #9/1/2010#, #10/31/2010#) = 1
- DateDiff("m", #10/1/2010#, #10/31/2010#) = 0
- DateDiff("m", #9/30/2009#, #10/1/2010#) = 13
- DateDiff("m", #9/1/2009#, #10/31/2010#) = 13
- DateDiff("m", #10/1/2009#, #10/31/2010#) = 12
You get the idea now. Just because the month number (or year number) has changed, DateDiff() considers the difference to be at least one even if only one day has passed between the two given dates. This makes the use of DateDiff() for age calculations a little tricky. Access should have provided a function similar to DateDiff() for calculating ages, but it does not.
So, we have to do the calculation using DateDiff(), but not rely on its results entirely. Note that the actual difference between the given dates (in the unit used in DateDiff()) is either equal to the answer returned by DateDiff(), or one less than that. Thus the true difference between 12/31/2009 and 1/1/2010 in years is actually 0 (which is one less than the answer returned by DateDiff()). But the true difference between 1/1/2009 and 12/31/2010 in years is actually 1 (which is the same as the answer returned by DateDiff()).
How do we know when the answer is actually one less than the number returned by DateDiff() and when it is equal to the number returned by DateDiff(). The answer lies in the use of the
DateAdd() function. If I add a year to 12/31/2009, the answer is 12/31/2010, which is more than 1/1/2010. If I add a year to 1/1/2009, the answer is 1/1/2010, which is less than 12/31/2010. However, if DateDiff() says the age is 0, then there is no further processing required (the age can never be negative). So, this tells us when we have to use the answer from DateDiff() as is, and when we have to subtract one from it to get the true age of something.
Given below is a function that calculates the age of anything given the unit in which the age is required (years, months, etc.), its "birthdate" (in the case of a person, this could be their actual birthdate, but in the case of other entities, it is the date when that entity came into being. For example, the transaction date could be the date used to figure out the age of a transaction, the date on a check could be used to figure out the age of the check, and so on), and the date on which the age is required (could be today's date, or any other date the user chooses to use).
The function uses the date on which the age is to be calculated as an optional argument. This is so that it can be used without that argument to return the age as of today (which is the most common date as of which an age would be required). Notice that the third argument is declared optional so that it can be omitted when calling the function. However, the optional argument has to be declared as a variant, not as a date. This is because the default value of a missing date argument is 12/30/1899 in Access, which is not what we want. Moreover, we can not set the default value to today's date in the function declaration (we can set it to some literal date, but we can not call the Date() function to set the default value inside the function declaration).
If we declare the argument of type variant, it does not get a default value. Instead, we can set its default value to today's date after checking whether the argument is missing or not using the IsMissing() function. So, we declare a variable of type date called AgeDate, inside the function. If the function is called with a third argument, we convert it to a date value, and set AgeDate equal to that value. If the function is called without a third argument, we set AgeDate equal to today's date. The rest of the function should be easy to understand given the discussion about DateDiff() above.
Function age(unit As String, startDate As Date, Optional endDate As Variant) as Integer
Dim AgeDate As Date
age = 0
If IsMissing(endDate) Then
AgeDate = Date
Else
AgeDate = CDate(endDate)
End If
If AgeDate < startDate then exit function
age = DateDiff(unit, startDate, AgeDate)
If age = 0 Then Exit Function
If DateAdd(unit, age, startDate) > AgeDate Then
age = age - 1
End If
End Function
Notice a couple of things about this function. Firstly, the units used are the same strings used by the DateDiff() and DateAdd() functions. So, if you want the age in years, unit has to be "yyyy", not "y" when you call age(). Secondly, since age can never be negative, we check whether the value of AgeDate is less than that of startDate. If it is, we just return a value of zero for the age.
If we want the age in months and years, we can actually use the function above to do that too. Since a year always contains 12 months, we can calculate the age in years and months using the construct below:
age("yyyy", startDate, endDate) & " years, and " &
age("m", startDate, endDate) - age("yyyy", startDate, endDate)*12 & " months"
But, what if we have to calculate the age in years, months and days? Since not every month has the same number of days, we can not use the construct above (which relies on the fact that a year is always 12 months long). Instead, we can use the construct below to do that:
age("yyyy", startDate, endDate) & " years, " &
age("m", dateadd("yyyy", age("yyyy", startDate, endDate), startDate), endDate) &
" months, and " &
age("d", dateadd("m", age("m", dateadd("yyyy", age("yyyy", startDate, endDate),_
startDate), endDate), dateadd("yyyy", age("yyyy", startDate, endDate),_
startDate)), endDate) & " days"
If you want the age in years and days, you can use the construct below:
age("yyyy", startDate, endDate) & " years, and " &
age("d", dateadd("yyyy", age("yyyy", startDate, endDate), startDate), endDate) &
" days"
I will leave it up to you, the reader, to figure out what the constructs above do, and how they do it! Perhaps to test your own understanding, you should try to come up with a construct like the above that tells you the age in months and days.
Hope this post has been helpful in solving any problems you might have had with age calculations in Access. The VBA code in this post has been tested in Access 2003 and should work without any problems in all versions of Access from Access 97 on up. If you have any problems or concerns, please feel free to let me know by posting a comment. If you have other questions on Access that you would like me to address in future posts, please feel free to let me know through your comments too. Good luck!
Take a look at the screenshot of the form to the left. The user can enter a date in the textbox at the top of the form (or have the program automatically generate a random date to enter in the textbox, or enter today's date in the textbox). The code behind the form then fills out the dates in the rest of the boxes on the form.
