Queries to Count Data

[Ibero]

Hello everybody,

What i'm trying to accomplish is getting these "count" formulas examples:

Under 5: Abs(Sum(IIf([AGE]<5 And [2008]=Yes,True)))

5-9: Abs(Sum(IIf([AGE]>=5 And [AGE]<=9 And [2008]=Yes,True)))

Right now they are telling me the counts correctly with the criteria that the data field "2008" is checked. I would like it to give me the counts but instead of the 2008 being checked criteria, with a date field and the criteria "between [enter start date] and [enter end date]. So the end goal is for the query / report to be run, the user entering a start and end date, and it giving me the respective counts (under 5, 5-9, etc.) with the between dates criteria for all of them... Is this possible? Thanks in advance to all who read.

-Ibero

 

[KenHigg]

Maybe something like:

Under 5: Abs(Sum(IIf([AGE]<5 And [2008]=Yes,1,0)))

 

[Ibero]

Well theres no more need to use [2008], it is a yes/no field. I'm trying to replace that with [Date]. I was playing around with something like:

Under 5: Abs(Sum(IIf([AGE]<5 And [DATE]=Between [Enter Start Date] and [Enter End Date],True)))

And i know this logic is off and it dosen't work, but in essence, this is what i'm trying to accomplish. Should i just remove the second part of the formula and just leave:

Under 5: Abs(Sum(IIf([AGE]<5,True)))

Then under the Date Field, enter the between commands in the criteria box?

 

[KenHigg]

So you're builder a totals query where you want it to return 1 row with a total count for each age group and a date range as a parameter?

 

[Brianwarnock]

Well theres no more need to use [2008], it is a yes/no field. I'm trying to replace that with [Date]. I was playing around with something like:

Under 5: Abs(Sum(IIf([AGE]<5 And [DATE]=Between [Enter Start Date] and [Enter End Date],True)))

And i know this logic is off and it dosen't work, but in essence, this is what i'm trying to accomplish. Should i just remove the second part of the formula and just leave:

Under 5: Abs(Sum(IIf([AGE]<5,True)))

Then under the Date Field, enter the between commands in the criteria box?

Yes , and in the totals row of what is in effect an aggregate query check expression in in the abs... column and where in the Datefield.

BTW as date is a function and therefore a reserved word in ACCESS never use it as a field name it will cause you problems at some time

Brian