calculating age from DOB and given date

# Thread: calculating age from DOB and given date

1. Junior Member
Join Date
Dec 1969
Posts
3

## calculating age from DOB and given date

Hi,<BR><BR>I have built an MS Access database and i have a field called "date_of_test", and another field called "date_of_birth". I need a field called "age_at_test" for the age of the person on the date of the test.<BR><BR>I have tried using... <BR><BR>=Year([Date_of_test])-Year([Patient_Date_of_Birth])<BR><BR>...but this returns an incorrect age in instances such as date_of_birth=31/12/2001 and date_of_test=01/01/2003<BR><BR>In the above example, the age is 1 but Access returns a value age=2. it is probably obvious to experts that this problem would arise, but i am only a novice so my knowledge of expressions isnt great:-(<BR><BR>any ideas?<BR><BR>Colly

2. Junior Member
Join Date
Dec 1969
Posts
3

## RE: calculating age from DOB and given date

sorry, the expression above should read <BR><BR>=Year([Date_of_test])-Year([Date_of_Birth])<BR><BR>Colly

3. Senior Member
Join Date
Dec 1969
Posts
10,852

## Are you doing this in an Access query?

If you are...Then the best way is just to use DateDiff.<BR><BR>Something like this:<BR>SELECT DateDiff("yyyy",Date_of_Birth,Date_of_test) AS Age FROM YourTable<BR><BR><BR>In ASP/VBScript it would be similar:<BR><BR>Age = DateDiff("yyyy",RS("Date_of_Birth"),RS("Date_of_te st"))

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## That won't work...

Read the docs.<BR><BR>DateDiff( &#039;y&#039;, #12/31/2002#, #1/1/2003# ) is 1 for example, even though it&#039;s actually only one day.<BR><BR>Try this little sample:<BR><BR>&#060;HTML&#062;&#060;BODY&#062;<BR >DateDiff("yyyy",#12/31/2002#,#1/1/2003#) is <BR>&#060;% = DateDiff("yyyy",#12/31/2002#,#1/1/2003#)%&#062;&#060;P&#062;<BR><BR>DateDiff("yyyy", #6/30/2002#,#6/29/2003#) is <BR>&#060;% = DateDiff("yyyy",#6/30/2002#,#6/29/2003#)%&#062;&#060;P&#062;<BR><BR>DateDiff("yyyy", #6/30/2002#,#6/30/2003#) is <BR>&#060;% = DateDiff("yyyy",#6/30/2002#,#6/30/2003#)%&#062;&#060;P&#062;<BR><BR>DateDiff("yyyy", #6/30/2002#,#7/1/2003#) is <BR>&#060;% = DateDiff("yyyy",#6/30/2002#,#7/1/2003#)%&#062;&#060;P&#062;<BR>&#060;/BODY&#062;&#060;/hTML&#062;<BR><BR>The only way to get an accurate measure is to do it yourself.<BR><BR>&#060;%<BR>diff = Year(date2) - Year(date1)<BR>If Month(date1) &#062; Month(date2) _<BR> OR ( Month(date1)=Month(date2) AND Day(date1)&#062;Day(date2) ) THEN<BR> diff = diff - 1<BR>End If<BR>%&#062;<BR>

5. Senior Member
Join Date
Dec 1969
Posts
10,852

## Hmmm....

Yep. Ugh. Stupid mistake.<BR><BR>Wasn&#039;t even really thinking about the implications, since she&#039;s trying to do it in Access, which severely limits her options. <BR><BR>Crap.

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## You *can* do it in Access...

...it&#039;s just ugly:<BR><BR>SELECT IIF( Month(startDate)&#062;Month(endDate) <BR> OR ( Month(startDate)=Month(endDate) AND Day(starDate)&#062;Day(endDate) ), <BR> Year(endDate)-Year(startDate)-1,<BR> Year(endDate)-Year(startDate)<BR> ) AS age,<BR> ...<BR><BR>Ehhh...maybe not *that* ugly...If you really needed it, why not?<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•