totals calculation

[potts]

I've tried to calculate the total sum of key controls contained in sub reports on a form. I have used this method several times before, but now something weird is happening.

The calculation is contained in an unbound control on the main form and is as follows:

=IIf(IsNumeric([Invoice3Access].[Report]![SubAccessFee])=True,[Invoice3Access].[Report]![SubAccessFee],0)+IIf(IsNumeric([tblCustomer].[Report]![SubSupFee])=True,[tblCustomer].[Report]![SubSupFee],0)+IIf(IsNumeric([Invoice1Charge].[Report]![Charge])=True,[Invoice1Charge].[Report]![Charge],0)+IIf(IsNumeric([Invoice1Prog].[Report]![SubProgFee])=True,[Invoice1Prog].[Report]![SubProgFee],0)

While this has worked fine before, in exactly the same circumstances and format, when customers are opened together the first report is correct with the unbound calculated field for subsequent customers displaying the total expected for the previous customer.

eg

Customer 1 expected total = £20 displayed total = £20
Customer 2 expected total = £30 displayed total = £20
Customer 3 expected total = £27 displayed total = £30

Does anyone know what might be causing this?

 

[neileg]

It would be easier to use Nz to turn your nulls into zeros, unless you can really have non-numeric values.

You have some spurious spaces but I don't know if this is why your formula isn't working, though!

=IIf(IsNumeric([Invoice3Access].[Report]![SubAccessFee])=True,[Invoice3Access].# [Report]![SubAccessFee],0)+IIf(IsNumeric([tblCustomer].[Report]![SubSupFee])=True,[tblCustomer].# [Report]![SubSupFee],0)+IIf(IsNumeric([Invoice1Charge].[Report]![Charge])=True,[Invoice1Charge].[Report]![Charge],0)+IIf(IsNumeric([Invoice1Prog].[Report]![SubProgFee])=True,[Invoice1Prog].[Report]![SubProgFee],0)

 

[Rich]

How are your subReports linked to the main report, and where on the report are they, detail/footer section etc? You don't need the =True either

 

[potts]

sub-reports ar linked via the CustomerID which appears on both the main and sub reports. Also, they are contained in the detail section.

 

[potts]

How do you use the Nz function?

 

[Rich]

Which section is the unbound field in?

 

[neileg]

Nz would make your formula look like this

=Nz([Invoice3Access].[Report]![SubAccessFee])+Nz([tblCusto
mer].[Report]![SubSupFee])+Nz([Invoice1Cha
rge].[Report]![Charge])+Nz[Invoice1Prog].[Report]![SubProgFee])

Which is a bit easier to read, and less prone to typos!

 

[Rich]

Nz doesn't work when refering to subreports that have no data, the IsNumeric function is a cheat workaround, the correct method is the HasData property, there have been examples posted here

 

[neileg]

Thanks, Rich. Happy to learn something new.