main report fails if subreports return nothing, need to show 0 if null

[Guy999]

Hi

I've been creating an invoice report that is to be printed out. It's using subreports and I have the problem of when the subreports don't have any data nothing is displayed in the main report, and therefore the summary fields in the main report error. I've tried using nz in my queries to get them to retun 0 but it doesn't seem to work.

As a last resort I've put the report up on my site in the hope that someone can help me as I've tried so many different things to get round this problem all to no avail and I've wasted a lot of my life as I don't know Access thoroughly. It's small at 46k.

http://www.guy.tubbs.btinternet.co.uk/reportforweb.zip

I've put some test data in it so anyone who takes a look will see my problem.

Here's how it is supposed to work:

1) Each company has a record in the company table.
2) When the system sends and email or SMS, the web system stores the company id and the SMS and or email in the report table.
3) A company gets charged according to what they've sent. If they send 1 SMS this charged for one message, if they send 1 email this is also charged as 1 message, but if they send 1 SMS and 1 email to the same person this is charged as only 1 message.
4) The 3 subreports do this count summary and are displayed on the main report; Number of emails only sent, SMS only sent and email & SMS sent to same person.
5) The main report then summarises the number of chargeable messages sent, given that SMS and email sent to same person is 1 not 2 chargeable messages.
6) Each company has 350 messages for their monthly charge of £40. Each message over this is charged at £0.12.

Test data is:
Company1 sent 1 email
Company2 sent 1 sms
Company3 sent 1 sms and email to same person
Company4 sent 1 sms and email to same person, 1 email to someone else and 1 sms to someone else.
Company5 sent nothing

If you run the main report you'll see that only the 4th company's invoice works as this is the only one where all the subreports return with data.

Thanks a lot for any help or guidance
Guy

 

[Garridon]

I did something similar--maybe you can find some good info from what I did. We had overdue records counted up on a subreport, and the totals were pulled into the main report. Obviously, if there weren't any overdue, I got a #Error on both reports.

On the main report, I created a hidden field with the following in it:

="Total Overdue: " & Reports!rptTrend!srpTrend!txtTotalOverdue

Then to pick up the total, I have visible field with the following in it:

=IIf(IsError([txtTotalOverdue]),"Total Overdue: 0",[txtTotalOverdue])

This converts the #Error to display a O instead.


Linda Adams
Linda Adams/Emory Hackman Official Web site

http://www.hackman-adams.com

Official web site for actor David Hedison:

http://www.david-hedison.com