#error showing where need zero

[NickyJay]

Hi,

I have a report that shows software subscription data and associated access codes. I am printing a report off showing customers' renewal costs for their whole software purchase - this could be 1 software with no need for access code and 1 software with access code.

The report has a subreport which shows the access codes so that they can check them (sometimes up to 23 access codes per software - postcodes). However, some customers in the report have software that does not require codes. Therefore I'm wanting to hide the "please check your codes..." bits for those not applicable and have put a count(accesscodes) to try to query if >=1 show the accesscode text. But... for those with no access codes I get the value #error for which I can't compare >=1 obviously ;0(

Thought this bit from another forum post may be on the right track but no joy ;0(
=IIf([HasData],count([CountCodes]),0)

any ideas how I make it show 0 if #error and can then use as comparison??

Thanks.

 

[dhookom]

If the text box is in the main report and it references a text box in the subreport then you use syntax like:
=IIf(subrptCtrlName.Report.HasData, subrptCtrlName.Report.txtTotal, 0)

If you can't figure this out, come back with the name of the subreport control on your main report as well as the name of the control in the subreport that you want to reference.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[NickyJay]

Thanks for responding (and Happy New Year by the way) ;0)

The main report is called rptBulkInvoiceLetters and has a text box that I want to check called txtFinalWebCount (if the value is 0 then this should hide the unrequited text).

This txtFinalWebCount references the sub report

subRptBulkInvoicesWebAccessCodes with a text box txtCountCodes which has the control source property set to

=Count([WebAccessCodes]) (WebAccessCodes being the proper access codes for the account - which could be empty for some customers)

Hope this all makes sense, and thanks in advance for your help!

Nic

 

[dhookom]

Does the subreport always return one or more records?
You might be able to use a control source of txtFinalWebCount like:

=IIf(subRptBulkInvoicesWebAccessCodes.Report.[HasData], subRptBulkInvoicesWebAccessCodes.Report.txtCountCodes,0)

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[NickyJay]

thanks for responding but I think my main problem is that I'm not quite sure where I put the Iif statement ;0( I've tried to put it in the control source of the main report's text box but it then comes up with the name error.

 

[dhookom]

My assumptions:
Subreport control on the main report: subRptBulkInvoicesWebAccessCodes

Textbox on subreport: txtCountCodes

The name of the textbox on the main report can't be the name of a field in the report's record source.




Duane MS Access MVP
Now help me support United Cerebral Palsy