COUNT IN REPORT

[Angelique]

Need to display the count of the courses that were "Confirmed" and "Cancelled" in the footer of the report. I tried using an unbound textbox and the expression builder to perform =count([Status].[Value] = "Cancelled&quot but obviously the syntax is incorrect because I get #Error.

Can anyone help?


Angelique

 

[JoeMiller]

You can use DCount to examine the table/query providing data to your report to get this information. Look it up in the help for a definition and usage.

Joe Miller
joe.miller@flotech.net

 

[Angelique]

Hi Joe,

I tried the DCount but again, I suspect I am missing something:

DCount("[Status]", "Training Details", "[Status] = 'Cancelled'&quot

Report won't run, missing operand? Any ideas with the syntax?


Angelique

 

[petelyons]

I recently had this same requirement for one of my reports. Here's what worked for me:

1. Create and save a Count query (assuming you know how).
2. Drag & drop this query into your 'master' report's footer area - then Access will create a subreport for you - so give it an intuitive name 'cuz you'll need to do some work on that subreport in a minute...
3. Close your 'master' report so you can edit the subreport.
4. Open the new subreport in Edit mode.
6. NOW use a =Sum([CalculatedFieldNameFromYourCountQuery]) expression for the textbox in the subreport.
7. Make that textbox invisible (assuming you know how).
8. Create a NEW textbox based on the calculated value of the invisible one by saying =([TheInvisibleTextboxName]) as the source for this new textbox.

Hope this helps, I can send you a file for you to examine if you e-mail me.

Pete Lyons
activeserver@hotmail.com

 

[JoeMiller]

Your syntax is fine, you need to put an = sign in front of it when using it as the source for a control. If you're still having problems with it after that then double check spelling!

Joe Miller
joe.miller@flotech.net

 

[Angelique]

Joe!

Worked great! Thanks


Angelique

 

[Angelique]

Hey Joe,

One problem, it's counting all the Cancelled, Confirmed for the entire table. I should mention that I am passing Start & End Dates as parameters and I only want the total count for that period of time.

Hope you can help!


Angelique

 

[JoeMiller]

You can add the dates as parameters to the dcount statement as well. Just add AND statements in the criteria part of the DCount expression like this:

DCount(&quot;[Status]&quot;, &quot;Training Details&quot;, &quot;[Status] = 'Cancelled' AND [StartDate] >= '1/1/01' AND [EndDate] <= '1/31/01'&quot

HTH

Joe Miller
joe.miller@flotech.net

 

[Angelique]

I think it's my fault, I should have provided you with more info.

I have a report that passes two parameters: [Enter Start Date] and [Enter End Date]. The Record Source for the report is the part that passes these parameters, not the unbound textbox that I want to place the count.

When I modified the syntax I got #Error. Any suggestions.

Yours

 

[JoeMiller]

Place two text boxes in your report like the following

ControlName: [bb]txtStartDate
ControlSource: [Enter Start Date]
Visible: No

ControlName: txtEndDate
ControlSource: [Enter End Date]
Visible: No

Now in your d Joe Miller
joe.miller@flotech.net

 

[JoeMiller]

Place two controls in the Report Header of your report like the following:

Control #1
ControlName: txtStartDate
ControlSource: [Enter Start Date]
Visible: No

Control #2
ControlName: txtEndDate
ControlSource: [Enter End Date]
Visible: No

Now modify your DLookup control to the following:

=DCount(&quot;[Status]&quot;, &quot;Training Details&quot;, &quot;[Status] = 'Cancelled' AND [StartDate] >= [txtStartDate] AND [EndDate] <= [txtEndDate]&quot

That should do it. HTH Joe Miller
joe.miller@flotech.net

 

[Angelique]

Hi Joe,

I finally got to giving your last code a go! I had to replace the [StartDate] and [EndDate] with my fieldname but it worked.

Thanks

Angelique