DCount with mathematical criteria, DateDiff function

[Guest]

I am trying to make a count of records meeting criteria for demographic
groups in a report. One section includes age brackets. I can get everything
to work using the DCount function in text boxes, but when I try mathematical
criteria, such as:

=DCount("[DTHI1]","program count q","[DTHI1] =true and [FY 05 Age]<21")

or =DCount("[DTHI1]","program count q","[DTHI1] =true and [FY 05 Age]
Between 21 And 40")

I get errors displayed in the text boxes.


I need to count clients within the following age brackets: under 21, 21-40,
41-65, 66-75, and over 75

[FY 05 Age] is a calculated field in a query using DateDiff(["yyyy",
[Birthdate], #6/30/2005#)

Does the DateDiff function not produce numerical values or something? What
am I doing wrong?

 

[Duane Hookom]

You are potentially doing several things wrong depending on your
requirements.

First off, using DateDiff("yyyy",...) to determine age isn't accurate. See
http://www.mvps.org/access/datetime/date0001.htm

You should not need to use DCount() if your report is based on "program
count q". I would expect that you could use something like:
=Sum( Abs([DTHI1]= -1 And [FY 05 Age] Between 21 And 40) )

This expression should work in a group or report header or footer. It would
not work in a page header or footer.

I also, would find a solution that didn't hard-code the age ranges into
expressions in control sources. If your age ranges change (most
specifications change) you shouldn't have to go back to design views to
change expressions.

 

[Guest]

thanks--I'll work on this.

You are potentially doing several things wrong depending on your
requirements.

First off, using DateDiff("yyyy",...) to determine age isn't accurate. See
http://www.mvps.org/access/datetime/date0001.htm

You should not need to use DCount() if your report is based on "program
count q". I would expect that you could use something like:
=Sum( Abs([DTHI1]= -1 And [FY 05 Age] Between 21 And 40) )

This expression should work in a group or report header or footer. It would
not work in a page header or footer.

I also, would find a solution that didn't hard-code the age ranges into
expressions in control sources. If your age ranges change (most
specifications change) you shouldn't have to go back to design views to
change expressions.

--
Duane Hookom
MS Access MVP
--

I am trying to make a count of records meeting criteria for demographic
groups in a report. One section includes age brackets. I can get
everything
to work using the DCount function in text boxes, but when I try
mathematical
criteria, such as:

=DCount("[DTHI1]","program count q","[DTHI1] =true and [FY 05 Age]<21")

or =DCount("[DTHI1]","program count q","[DTHI1] =true and [FY 05 Age]
Between 21 And 40")

I get errors displayed in the text boxes.


I need to count clients within the following age brackets: under 21,
21-40,
41-65, 66-75, and over 75

[FY 05 Age] is a calculated field in a query using DateDiff(["yyyy",
[Birthdate], #6/30/2005#)

Does the DateDiff function not produce numerical values or something?
What
am I doing wrong?