Understanding DCount() Syntax

[3734kumo]

I am designing a report with group levels and the sum() function works just fine. It is in group-footer & resets for each group. I need to count the occurrance of specific TEXT in a field, say DRINK in file CLIENTS within each group. There are a finite number of drinks, such as JUICE MILK etc. I have reviewed every question (I think) & the exact expression eludes me. The latest one I have is:

=DCount("DRINKS","CLIENTS","[DRINK]='JUICE'&quot

This yields a value which appears to be over the entire report, and is the same in every grouping.

What is wrong? I have been programming for about 40 years and am just learning Access for a volunteer application.

 

[3734kumo]

Correction: DRINK is used throughout, not DRINKS

 

[hsitz]

There's nothing dynamic about your dsum function; it doesn't readjust depending on the particular data in each group.

To make it dynamic, you're going to have to reference a DRINK value, rather than just hard-coding in something like 'JUICE'.

Here's how you would probably do it:

=DCount("DRINK","CLIENTS","[DRINK]= '" & [DRINK] & "'&quot

The third argument in the above function will be constructed to include the particular drink of the record the report is processing at the time the dsum function is called. -- Herb

 

[3734kumo]

Thanks Herb, but this does not solve my problem. I know I'm missing something simple. The result I want is to count the 'types' of DRINK in each group (this is for an application where meals are delivered on different routes. Thus, for each route (which is the group) I need to count the DRINK value so that after each group I can print:
DRINK JUICE 10
MILK 5
etc., and then reset them for the next group. I guess I don't know the manner in which Access counts and resets.

Thanks for any help.

Mitch