Date Calculations

[ticktock]

Hi

I was wondering if somebody could help with a problem I have come across. I am currently developing a database for a small charity that I work for. The aim of the database is to record when staff undertake counselling.

I have created a query that calculates the current age of a client in the database uing the following expresion:

Age: Date()-[Date of Birth]

The Date of Birth is formated as a Short Date

In the query I have set the format of the column in the query to 'yy'. This then just displays the age of a client. This work fine apart from ages which are less than 30. If the age is less than 30 e.g. 29 it displays the date as 1929.

Does anybody know how I might be able to solve this or an alternative way in which to calculate dates and display it as 'yy'. (Its been a few years since I last work with access so skills a little rusty)

The reason behind the yy is that the monthly & yearly stats have to group by age ranges.

I am using Access XP (2002) SP2.

Many thanks in advance

Tony

 

[Ziggy1]

Try :

Years: Year(Date())-Year([dob])

 

[Jon K]

An expression for returning Age:

Age: DateDiff("yyyy",[DateOfBirth],Date())+(Format(Date(),"mmdd")<Format([DateOfBirth],"mmdd"))
.

 

[ticktock]

Re: Date Calculations - Thanks

Hi

Thanks very much for the response Ziggy1 & Jon K. I have tried both and it appears that Ziggy1 works better. For some reason Jon formula only returned values of 1900 for all the records. I will have a look to see if I can work it out though.

Thanks once again I really appreciate the response.

Tony

 

[EMP]

Jon's expression should be able to return the correct ages. For an example, you can see his query in the attachment in this thread:-
http://www.access-programmers.co.uk/forums/showthread.php?t=103312


Using Year(Date())-Year([DateOfBirth]) only deducts the year portion of one date from the other. Hence in many cases it fails to accurately represent the age. For instance,

Age: Year(#1/1/2006#) - Year(#12/31/2005#)

returns the age of 1 because 2006-2005 equals to 1 although actually only one day elapsed.

^