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.

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


"Tanya" wrote:

> 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.
>
>

On 26 Jan 2006 08:42:31 -0800, Tanya wrote:

> 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.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

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?

On 2 Feb 2006 11:38:31 -0800, Tanya wrote:

> 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"?
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail