Count Problem 1

[jleboeuf]

Hi!,
it's the first time i use Access to create reports and i don't figure how to make a count for a specific value

my problem,
I have a weekly report, where i have the number of days between two dates:
DOCKET DAYS
1 1
2 2
3 1
4 3
5 2

What i want in my report footer
1 day = 2 records
2 days = 2 records
3 day = 1 record

i try this: =DCount("[DAYS]";"Estlog";"[DAYS] = 2&quot work fine but for all the table not only for the records i have on my report.

thx for help

 

[RickSpr]

DCount goes directly to the database tables, not to the report's recordset, so it finds all matching records in the table. You need to modify the DCount criteria ([DAYS] = 2) to include the criteria that are in effect for your report's recordset. That is, you need to combine "[DAYS] = 2" with the WHERE clause from the report's record source, and with any Filter that you might have active on the report.

A simpler and more efficient method, though, would be to use a Report Footer. Add a text box to the report Detail section for each counter you need, making them invisible. In the Control Source property, use an IIf function to set the value to 1 if the record should be counted by the corresponding counter, else to 0. In the report footer, use the Sum function to add up the values for each counter. For example:
<Report detail section>
Text box name = &quot;Days1Counter&quot;
Control Source = &quot;=IIf([Days] = 1, 1, 0)&quot;
Text box name = &quot;Days2Counter&quot;
Control Source = &quot;=IIf([Days] = 2, 1, 0)&quot;
Text box name = &quot;Days3Counter&quot;
Control Source = &quot;=IIf([Days] = 3, 1, 0)&quot;

<Report footer section>
Text box name = &quot;Days1Count&quot;
Control Source = &quot;=Sum(Days1Counter)&quot;
Text box name = &quot;Days2Count&quot;
Control Source = &quot;=Sum(Days2Counter)&quot;
Text box name = &quot;Days3Count&quot;
Control Source = &quot;=Sum(Days3Counter)&quot;
Rick Sprague

 

[jleboeuf]

Thx for the reply

I try the solution, but when i run the report i have a enter parameter value box appear and ask me a value for Days1Counter field in my repport footer. should i add the field in my table ?

thx

 

[RickSpr]

I apologize! I was thinking you could refer to detail controls in the form footer, but you can't, you can only refer to fields in the record source. Even though I researched my answer in the Help file, I didn't pick up on that. (It's amazing how little info Microsoft gives us on using the Header and Footer sections!) I should have tested my answer, but I was in a hurry.

Forget about the text boxes in the detail section. If you still have them, delete them. But keep the report footer text boxes. Here's what I should have given you:

<Report footer section>
Text box name = &quot;Days1Count&quot;
    Control Source = &quot;=Sum(IIf([Days] = 1, 1, 0)&quot;
Text box name = &quot;Days2Count&quot;
    Control Source = &quot;=Sum(IIf([Days] = 2, 1, 0)&quot;
Text box name = &quot;Days3Count&quot;
    Control Source = &quot;=Sum(IIf([Days] = 3, 1, 0)&quot;

I did test this code, so I know it works. Rick Sprague