Search The Web

Today's Headlines

Friday, November 19, 2010

Microsoft Access Tips & Tricks: Calculating Age

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!

6 comments:

L.H. said...

Thanks for that.

Why can't i put a criteria after using that function in a query?

I am trying to put a criteria ex : <25 and it says something about that criteria not being right for that type of data... function is as integer, i don't get it.

Thanks

Blogannath said...

Can you post the SQL of the query you are trying to write (just include relevant fields so that we don't have to wade through irrelevant stuff while we debug the issue at hand) so that we can look at what is going on? Also, without the criterion, is the query returning the correct values in that column (I am trying to figure out if there is something wrong with the way the function is being called in your database).

Cornel Chibona said...

Hi,
Nice post, it really helped me out, but I have a condition to find diff in days and hours, where user input only hours in text boxes, and it can be like from 26 August, 2013, 20:50 to 30 August, 2013, 23:50, when I use date diff I get 3 hour diff, it doesn't count hours for the days, how to go around with that...?

Blogannath said...

I am not sure I understand your problem. Are your users entering only the times or are they entering the full date and time in the text boxes. If you have the full timestamps, then a datediff() with those full timestamps should give you the difference including the days.

For instance, this is the output from the immediate window when I try to use datediff() across multiple days:

?datediff("h", #08/01/2013#, now())
662

Joni said...

Can you tell me what do I write in criteria if I want to query persons who are between the ages of 25 to 45?

Blogannath said...

Assuming you have the function named as Age() in your database, you can use a query like below to accomplish what you want:

select * from myTable where Age("yyyy", myDatefield) between 25 and 45

Visitors Country Map

Free counters!

Content From TheFreeDictionary.com

In the News

Article of the Day

This Day in History

Today's Birthday

Quote of the Day

Word of the Day

Match Up
Match each word in the left column with its synonym on the right. When finished, click Answer to see the results. Good luck!

 

Hangman

Spelling Bee
difficulty level:
score: -
please wait...
 
spell the word:

Search The Web