Age Demographics

[Lost 1]

I am having a problem with age and date ranges.
I need to create a report that shows the total number of volunteers
that are within the following age ranges:
under 15
15-19
20-24
25-34
35-44
45-54
55-64
65-74
75-84
85-94

I currently have a table that has the min and max for the age ranges
and the volunteer table has the birthdate - the age is calculated from
the birthday when entered in the volunteer form. I am stuck on how to
show the number of volunteers within each range when a date range is
entered. It seems to work without the date range but once the date
range is set as a criteria it doesn't work right.

The output should look like this:

Form (Report Date Range):
Begin Date:12-1-2000
End Date: 12-1-2003

Report (rptAgeRanges):
Age Range Total #
under 15 5
15-19 10
20-24 15
25-34 25
35-44 25
45-54 25
55-64 20
65-74 20
75-84 10
85-94 5
Grand Total: 160

Thanks in advance!

 

[Allen Browne]

You could type a subquery into the Field row of the query that is the source
for your report.

Something like this:

AgeRange: ( SELECT TOP 1 RangeID FROM tblAgeRange WHERE
DateDiff("yyyy", [BirthDate], Date()) + (DatePart([BirthDate]) <
DatePart(Date())) >= tblAgeRange.MinAge )