Dcount Syntax

[Chris Strug]

Hi,

Sorry if this is an obvious question but, I have a report. It's fairly
simple, just a list of ID's grouped by a company.

However, I'd like to include a count of the containers for each company in
the company group footer.

Thus far I have decided to use "DCount", however for the life of me I cannot
get it to work. Below is exactly what I have entered in the Data field for a
text box in the footer:

DCount("qryLongStayContainers","ContainerNo","Customer = '" & [Customer] &
"'")

I have checked and there are no typos! The source of the data is the query
named "qryLongStayContainers", and I'm trying to count the number of
containers for the specified customer.

If anyone could point out where I'm being thick I'd be very grateful.

Kind thanks

Chris Strug

 

[Fredg]

Chris,
Hard to tell which is the field name and which is the table/query name from
your code.
As written, [qryLongStayContainers] is a field name, [ContainerNo] is the
table or query name, and [Customer] is a criteria field of Text DataType,
not Number.

To count a field used in the report.
=Count([FieldName])

So if the Report includes the [qryLongStayContainers] field:
=Count([qryLongStayContainers])
Placed in the Group footer it will give you the count for each Group.

If the [qryLongStayContainers] is not included in the report,
then you can use DCount to count instances in the table or query.

I notice you posted that you grouped the report by ID ([Customer] ?).
If the ID is a Number datatype, then your DLookUp was incorrectly written.
It should have been:
=DCount("[FieldName]","[Table or Query Name]","[Criteria Field] = " &
[Criteria Field on Report])

which, if the fields and table names are in the correct position on your
sample:

= DCount("qryLongStayContainers","ContainerNo","[Customer] =" & [Customer])

See Aggregate functions in Access Help.

Hope this has helped.