Hi all, im wondering if you can provide a little help on this probably trivial matter.
I am struggling to get a total box on an invoice I have built to function correctly. The invoice total is a sum of four seperate total fields which are located at the footers of four sub reports in the invoice.
Now, this works perfectly if all of the four reports are visible due to having data. However, as I have set the 'can shrink' option to yes if the subreport dosent have data it will not appear. This is causing the text box which sums to go a bit silly.
I realise that this is to do with the fact that one of the parts of my function dosent exist (the sub report isnt displaying) but I cant figure out a way of just setting that part of the function to 0 if the sub report isnt showing so the sub can continue.
I was playing with NZ functions (and i think this is the right idea) but I cant get the bloody thing to work.
Can anyone spot any onvious cockups in the below formula?
=Nz([rptSubReportInvoiceAdditionalCostsVAT].[Report]![Total],0) + Nz([rptSubReportInvoiceAdditionalCostsNoVAT].[Report]![Total],0)
+ Nz([rptSubReportInvoiceBillingUpdatesVAT].[Report]![Total],0) + Nz([rptSubReportInvoiceBillingUpdatesNoVAT].[Report]![Total],0)
This is a little annoying. I think i may have used the NZ functions in the wrong way and would very much appreciate some help!
Kevin.
I am struggling to get a total box on an invoice I have built to function correctly. The invoice total is a sum of four seperate total fields which are located at the footers of four sub reports in the invoice.
Now, this works perfectly if all of the four reports are visible due to having data. However, as I have set the 'can shrink' option to yes if the subreport dosent have data it will not appear. This is causing the text box which sums to go a bit silly.
I realise that this is to do with the fact that one of the parts of my function dosent exist (the sub report isnt displaying) but I cant figure out a way of just setting that part of the function to 0 if the sub report isnt showing so the sub can continue.
I was playing with NZ functions (and i think this is the right idea) but I cant get the bloody thing to work.
Can anyone spot any onvious cockups in the below formula?
=Nz([rptSubReportInvoiceAdditionalCostsVAT].[Report]![Total],0) + Nz([rptSubReportInvoiceAdditionalCostsNoVAT].[Report]![Total],0)
+ Nz([rptSubReportInvoiceBillingUpdatesVAT].[Report]![Total],0) + Nz([rptSubReportInvoiceBillingUpdatesNoVAT].[Report]![Total],0)
This is a little annoying. I think i may have used the NZ functions in the wrong way and would very much appreciate some help!
Kevin.