DCOUNT and DateRange Parameter

Megan

Hi Everybody-

I've been reading some of the posts about DCOUNT, and I haven't yet
found an answer; so, I'm posting this question.

I have a report that I'm trying to use DCOUNT on to compute the
percentages of each group. The groups are based on sports. For
instance, football, soccer, baseball, basketball. I have a table of
people that play these sports.

My report groups the sports people play. For example, my report may
look like this:
GROUP Header 1

Basketball:
Michael Jordan
Kobe Bryant

GROUP Footer 1
Total: 2 (Using the simple Count(*) in the Group
Footer)

GROUP Header 2

Soccer:
David Beckham

GROUP Footer 2
Total: 1

REPORT Footer

Grand Total: 3 (I'm using Count(*) again here)

Then in the Footer of my report, I use the Count(*) function again to
compute the total number of records. Then, I use the DCOUNT function
to compute the percentages of people that play each sport.

=DCount("[Sport]","qrySports","[Sport] = 'Basketball'")/Count(*)

So, for "basketball," I would get: 2/3 = .667

HOWEVER...when I try to use a Date Parameter as a criteria in my
query, I get an error (#ERROR).

In the criteria for my "Date" field, I'm using: Between [Beginning
Date] And [Ending Date].

I've read a few posts where some people have tried a few different
things, such as creating multiple queries or creating a form to use.

So, I guess my question is: Will DCOUNT work if I add extra code to
it?

For example:

=DCount("[Sport]","qrySports","[Sport] = 'Basketball'")/Count(*) And
Queries!qrySports![Enter Beginning Date] & " to " &
Queries!qrySports[Enter Ending Date]
OR is there a better way to accomplish this?

Thanks everybody!

Megan

Nov 13 '05 #1

Salad

Megan wrote:

Hi Everybody-

I've been reading some of the posts about DCOUNT, and I haven't yet
found an answer; so, I'm posting this question.

I have a report that I'm trying to use DCOUNT on to compute the
percentages of each group. The groups are based on sports. For
instance, football, soccer, baseball, basketball. I have a table of
people that play these sports.

My report groups the sports people play. For example, my report may
look like this:
GROUP Header 1

Basketball:
Michael Jordan
Kobe Bryant

GROUP Footer 1
Total: 2 (Using the simple Count(*) in the Group
Footer)

GROUP Header 2

Soccer:
David Beckham

GROUP Footer 2
Total: 1

REPORT Footer

Grand Total: 3 (I'm using Count(*) again here)

Then in the Footer of my report, I use the Count(*) function again to
compute the total number of records. Then, I use the DCOUNT function
to compute the percentages of people that play each sport.

=DCount("[Sport]","qrySports","[Sport] = 'Basketball'")/Count(*)

So, for "basketball," I would get: 2/3 = .667

HOWEVER...when I try to use a Date Parameter as a criteria in my
query, I get an error (#ERROR).

In the criteria for my "Date" field, I'm using: Between [Beginning
Date] And [Ending Date].

I've read a few posts where some people have tried a few different
things, such as creating multiple queries or creating a form to use.

So, I guess my question is: Will DCOUNT work if I add extra code to
it?

For example:

=DCount("[Sport]","qrySports","[Sport] = 'Basketball'")/Count(*) And
Queries!qrySports![Enter Beginning Date] & " to " &
Queries!qrySports[Enter Ending Date]
OR is there a better way to accomplish this?

Thanks everybody!

Megan

I don't know if this will work.

Instead of /Count(*), use the name of the text field that contains the
count.

=DCount("[Sport]","qrySports","[Sport] = 'Basketball'")/[FooterCountField]

Nov 13 '05 #2