DCount function 1

[Evil6]

I'm trying to retrieve the number of records on a report based on a query, however, the function gives back #Error.

I'm trying to use this function incorporated on a report, using a text field and constructing the expression.

The access help suggest the sintax dcount=("*","tablename") and, in fact, this works. But, when I try to replace the "tablename" with "queryname", the error occurs.


Any ideas on this?

Thank you.

 

[BobStubbs]

I think this fails because the * means 'all fields in a table', so does not work in a query. You can count records in a query by counting any field which is listed in the query. Here is an example from my Access notes:

In a report, you can base a DCount function on the query used to create the report. This avoids having to re-specify all the selection criteria used in the query, and keeps the DCount results in line with the rest of the report. Example:

=DCount("[SalesValue]","qryRegionQuery","[Region]='North'")

Counts all records listed where the [Region] field contains the value 'North'. This is useful where you want to create (for example) a list of totals by region, at the end of a report which is grouped in some other way.

As always, you have to be very careful with the various brackets and quote marks!

I hope that this helps.




Bob Stubbs

 

[Evil6]

Hi, BobStubbs. Thank you for your answer.

No, that didn't work. I reformed the expression to

=DCount("[fieldname]", "queryname")

and it still doesn't work. As I said, if I change "queryname" to "tablename", it works great. Like this, it doesn't.

 

[BobStubbs]

OK - I agree this is very strange. I have just run another test, based on a very simple query which just lists employee numbers and names from one of my database tables, and all these DCount statements work:

=DCount("[EmployeeID]","qryTest1","[EmployeeSurname]='Jones'")

=DCount("[EmployeeID]","qryTest1")

=DCount("[EmployeeID]","tblEmployees","[EmployeeSurname]='Jones'")

Can you please tell me - what Access version you are using, and the exact names of your table, query, report and relevant fields? I wonder if you have some type of problem with an invalid or duplicated object name ...



Bob Stubbs

 

[Evil6]

Hello Bob.

You are absolutly right. The problem was a duplicate name. Both table and query were named the same and somehow, access (2003, btw) was getting mixed up. I've got it all worked out now.

However, I couldn't have done it without your help. Thank you very much.

 

[TroyMcClure]

You can count records in a query by counting any field which is listed in the query

just a caveat...for a true count the field must be non-null in all records, DCount (or any aggregate function, for that matter) will skip null fields. It's best to use a key field if counting.

It may seem obvious but I've seen many a report with badly skewed averages due to this mistake.
--T