counts and totals 1

[stormcloudgathering]

I need to display the total in a report showing the number of records in the recordset.
The records are dates showing when a specific request was made to a client, when the request was answered, if it was late and if so by how much.
I have placed a text box in the footer of the report and types =count(*), and I get #error.
Is this to do with the field properties in the query and the report?
If there is only one record in the recordset then the total works....
any ideas please?
many thanks
lynne

 

[CosmoKramer]

Lynne,

Try your count statement, but instead of the asterisk substitute one of your detail fields; something like:

=Count([ClientRequestDate])

 

[stormcloudgathering]

Thanks, but it still didn't work, instead of getting #error, I now get an empty field.
Just as an aside, in the query I have put a criteria in one of the fields to return only certain records, could this be the reason?

 

[CosmoKramer]

This should work as long as the field you are counting is a bound control, the criteria doesn't matter. Do you have a field in the detail section that is a bound control??

 

[stormcloudgathering]

The field I've put in square brackets is definitely a bound control. I still get an empty result.....
Is this anything to do with the field format in the table, the query, the report, or is this a red herring?
I've even started both the query and the report again from scratch and still nothing!
I feel sure I'm doing something wrong at a really basic level...
Many thanks
lynne

 

[CosmoKramer]

Can you send me a zipped copy of the db so I can take a look at it??

sw3540@yahoo.com

 

[CosmoKramer]

Lynne,

I think I see the problem. You are looking for a count of the entire report, and your calculated control is in the page footer. Insert a report footer into your report and move your Text25 text box to the report footer. Change its Control Source to:

=Count([Request_Sent])

Then you should be all set.....Let me know if this helps or not.

Steve

 

[stormcloudgathering]

oh my goodness!
it works!
thank you so much!

I've given you a star....

Please can you tell me why I don't seem to be able to get the average to be formatted to 2 digits no matter what I do?

(I know, I'm cheeky, but a girl has to ask!)

thanks again
lynne

 

[CosmoKramer]

You're welcome.....Thanks for the star....

Did you try changing the format of that text box to Fixed???

 

[stormcloudgathering]

Forgive the delay in responding - I had to have a lie down, I've been fiddling with this for aeons and now you've made it work - the milky bars are on me
Big thanks
lynne

(You're going to regret helping me, I have a few more questions!)

 

[stormcloudgathering]

My user wants to be able to specify the dates upon which she wants to report. When I create a parameter query, the totals and averages turn into #error...
Is there a way to specify the required dates, either in the query or in the report, so that the totals and averages still work?
thanks
lynne

 

[CosmoKramer]

Is TxtAvg still in the page footer?? If so, move it to the report footer also. Make sure its control source is:

=Avg([How_Late])

Hope this helps......

 

[stormcloudgathering]

fantastic - it all works now!
thank you so much!

 

[stormcloudgathering]

when there are no results to display, I get the #error again, is there a way of saying "if there are no results, then leave it blank, don't display the #error"
thanks
lynne

 

[CosmoKramer]

Probably the best thing to do is not generate the report at all if there is no data. In the On No Data event of your report put something like this:

MsgBox ("There are no requests for the date range specified")
Cancel = True

A message box will inform the user that there are no records and then return them to the menu.

 

[stormcloudgathering]

again, thanks very much, I'm now considered to have earned my salary today!