DCount in a Report with Parameter

[Tanya]

I am using a query with a parameter as the source for a report. The
parameter prompts for the date a class was held and the class code. In
the report I want to calculate the number of people who registered for
and actually attended that class. I was trying to use the following
DCount function, but I keep getting an error message:
=DCount("[Registrants]","Class ListQry","[Date]=[Enter class date]").
Registrants is the name of the field I want to count, Class ListQry is
the query name and [Enter date] is the parameter as it appears in the
query. Any idea why this is creating an error. Thanks in advance.

 

[Guest]

I've not dealt with it, because of using naming conventions, but I think you
may need brackets around your query name, to accomodate the space in the name.

Access does not like spaces in its names.

Sharkbyte

 

[fredg]

I am using a query with a parameter as the source for a report. The
parameter prompts for the date a class was held and the class code. In
the report I want to calculate the number of people who registered for
and actually attended that class. I was trying to use the following
DCount function, but I keep getting an error message:
=DCount("[Registrants]","Class ListQry","[Date]=[Enter class date]").
Registrants is the name of the field I want to count, Class ListQry is
the query name and [Enter date] is the parameter as it appears in the
query. Any idea why this is creating an error. Thanks in advance.

Several mistakes.
1) Your table name ("Class ListQry") contains a space. Enclose the
name within brackets inside the quotes, i.e. "[Class ListQry]".

2) Date is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

3) The [Enter Class Date] parameter prompt must be concatenated into
the criteria, otherwise Access is looking for a literal "Enter Class
Date" value.

4) A date value must be enclosed within the date delimiter symbol #.

Try:
=DCount("[Registrants]","[Class ListQry]","[DateField] = #" & [Enter
Class Date] & "#")

Note: I've used a generic [DateField] in the Where clause. Change it
to whatever name you change your [Date] field to.

 

[Tanya]

Fred,

Thanks for the pointers. I changed the field name from Date to
ClassDate and edited the DCount function as you recommended. It reads:
=DCount("[Registrants]","[Class ListQry]","[ClassDate]=#" & [Please
enter class date] & "#"). Unfortunately, I still get #Error when I
preview the report. I put the control containing this function in a
group footer. Any ideas?

 

[fredg]

Fred,

Thanks for the pointers. I changed the field name from Date to
ClassDate and edited the DCount function as you recommended. It reads:
=DCount("[Registrants]","[Class ListQry]","[ClassDate]=#" & [Please
enter class date] & "#"). Unfortunately, I still get #Error when I
preview the report. I put the control containing this function in a
group footer. Any ideas?

The DCount syntax looks OK to me.

I just ran a test in a report and I got the correct count from a
query.

1) Is a complete valid date being entered, i.e. 1/15/2006?

2) Make sure the Name of this control is NOT "Registrants" or
"ClassDate". Access gets confused when the name of the control is the
same as the name of any field in it's control source.

3) Are there fields in the query named "Registrants" and "ClassDate"?