unsuccesful DateDiff results

  • Thread starter Thread starter smc
  • Start date Start date
S

smc

I've created a query using access 2000 to determine a count of
differently-aged individuals included in the corresponding table.
As a field I have : Age: DateDiff("yyyy",[data]![DOB],[Stats Date]![Stats
End Date]), where the "Stats End Date" corresponds to the specific cut off
date (in this case 06/30/04); in the criteria cell for this field, I
indicate the parameters of the age group, e.g., ">=4 And <12." Both DOB and
Stats End Date use input mask 99/99/00;0.

What I wanted (in this case) as a result is a list of those who are at least
4, but less than 12 years old, as of the cutoff date. This would exclude
those who are 3 years +9 months. But for reasons I don't get, the query
results include those whose DOB = 09/30/04. I've tried altering the criteria
to be more specific ">=4.00 And <11.99," but that doesn't yield any results
at all.

Help with this would be much appreciated.
 
Correction: the confusing DOB= 9/30/00, ie, some one who would not be 4
years of age until after the end date.
 
Well, the reason for the problem is that the Datediff function counts the number
of boundaries crossed. So when that the difference in years between Dec 30,1989
and Jan 1, 1990 is ONE year even if the difference is only two days. One way to
accomplish what you want is to used something like the following


Field: DOB
Criteria: > DateSerial(Year([Stats Date]![Stats End Date])-12, Month([Stats
Date]![Stats End Date]),Day([Stats Date]![Stats End Date])) AND
<= DateSerial(Year([Stats Date]![Stats End Date])-4, Month([Stats
Date]![Stats End Date]),Day([Stats Date]![Stats End Date])-1)

Or criteria of
Criteria: > DateDiff("YYYY",-12,[Stats Date]![Stats End Date])) AND <=
DateDiff("YYYY",-4,[Stats Date]![Stats End Date]))

Another option is to use a more accurate function to calculate Age

Age: DateDiff("yyyy",DOB,[Stats Date]![Stats End Date]) + Format(DOB ,"mmdd") >
Format([Stats Date]![Stats End Date],"mmdd")

This will subtract 1 if the from the years if the birthday for the individual
has not yet occured. Then you can use your original criteria.
 
Back
Top