Counting students in age range

[crododo]

Hi.

I have this formula for counting age groups

TOTAL 29 - 31: Count(IIf(DateDiff("yyyy";[DATE_OF_BIRTH];Date())>=29<=31;1))

But id does not work, if I use between 29 and 31 also do not work

I need to get count (number) of all students that have 29,30 and 31 year of age, any idea where I am making mistake?

Thanks for anwsers.

 

[namliam]

TOTAL 29 - 31: Sum(IIf(DateDiff("yyyy";[DATE_OF_BIRTH];Date())>=29 and DateDiff("yyyy";[DATE_OF_BIRTH];Date())<=31;1;0))

 

[crododo]

Dear Namlian,

This is exactly what I need.

Thanks, sometimes I get supprised how stupid things makes so much probblem

Thanks

 

[namliam]

Things like this can turn into a maintenance nightmare though.... now you have a 3 year bracket, what if your requirements change to 4 or 5 year brackets?
As long as you always have 3,4,5 year brackets you can actually calculate the brackets and use a crosstab query to "convert" the values into columns.
i.e. format(Int([age]/5)*5 , "00") & "-" & format(Int([age]/5)*5+4 , "00")
But you can make different ones to suite your need

If you have mixed brackets, i.e. 24-28, 29-31, 32-40, you cant really calculate it, instead perhaps you want to make a related table from with ages 1 to 150 (or whatever) and the respective brackets they belong to....
Or a function that converts age into different brackets ... in both situation again using a crosstab to convert the resulting values/rows to columns.

 

[Brianwarnock]

Your method of calculating age is incorrect as it should check for birthdays having been reached. Datediff merely subtracts the years.



Code:
DateDiff("yyyy",[dob],date())+(Format([dob],"mm/dd")>Format(date(),"mm/dd")


Brian

 

[namliam]

True that, didnt even check/notice it... or use int(Format(date() - [dob], "YY")), should work too