Dcount in report with parameter again

[Tanya]

After much work and with help from this forum, I'm almost embarrassed
to say I finally got the dcount function which included a date
parameter to work in a report (This is a continuation of a post of the
same subject that apparently was archived due to inactivity). I am
still having a problem with the second parameter which is prompting for
a class code, which is typically 3 characters. Thanks to an earlier
post I was told parameters need to be concatenated otherwise Access
will read them literally, but I can't seem to get it right. This is
what I have so far:

=DCount("[Registrant]","ClassRosterQry","[ClassID]="&[Enter class
code]&")

which isn't working. Registrant is the name of the field I want to
count, ClassRosterQry is the query that provides the data, ClassID is
the criteria field and Enter class code is the actual parameter. Any
help would be GREATLY appreciated. Thanks,

 

[Ken Sheridan]

If ClassID is a number use:

=DCount("[Registrant]","ClassRosterQry","[ClassID]=" & [Enter class
code])

If its text use the following to warp its value in quotes:

=DCount("[Registrant]","ClassRosterQry","[ClassID]=""" & [Enter class
code] & """")

Unless the Registrant field can be Null and you want to omit the Nulls from
the count you don't need to specify a field, just use:

=DCount("*","ClassRosterQry", etc.

As you clearly have several parameters in play here you might like to
consider using a single form for them all and then opening the report with a
button on the form. It also means you are not limited to the rather basic
system generated prompt but can use a combo box for instance to select a
class, in which case the expression would be along thse lines:

=DCount("[Registrant]","ClassRosterQry","[ClassID]=" &
Forms![YourForm]![cboClasses])

You'd put controls for the other parameters on the same form and reference
then in the same way, either on the report or in its underlying query.
Another advantage of using a single form for the parameters is that you can
include validation code, e.g. to check that the start date of a date range is
not later than the end date, or simply that no parameters have been left Null
(unless they are optional parameters of course)

If you use a form and want to close it automatically do this in the report's
Close event procedure with:

DoCmd.Close, acForm, "YourForm"

Ken Sheridan
Stafford, England