DCount problems

[gpass]

I'm having problems with Dcount when used in report group footer. Report is based on query and grouped by 'Location' dislayed by txtLocation control. I need to use DCount to bring up the total number in the group location but from tblMain and not the query that report is using. How do you reference the txtLocation control as criteria in DCount. How to DCount using this:
=DCount("Location]", "tblMain", use the value in report txtLocation)
using =DCount("[Location]","tblMain","Location") brings up total for all group records. I just need it to show the number for whatever is displayed in txtLocation group.

Your help is greatly appreciated.

 

[stopher]

Your statement should look like this:

=DCount("[Location]","tblMain","[Location]='" & [Location] & "'")

In English this says "count the occurences of location from table tbleMain where Location (in the table) equals Location (from the report)"

Note that the last [Location] in this statement is refering to the use of [Location] in your report. If the statement is at Location group level then it will reference the Location at that level.

Also note the use of single speech marks and & to build the where expression. It will be interpretted as:
[Location]='London'
(or whatever the location is for the current group)

hth
Stopher

 

[gpass]

Stopher:
I sure do appreciate your help and also the explaination. Just what I needed.