Calculate total conditionally

[davidg47]

I have two fields that I need to calculate a total for, but only if both fields have a value in them.
The fields are:
1.) ChargesThisStatementPeriod
2.) CreditsThisStatementPeriod
3.) CreditsDueAmount (totals calculated field)

Currently I am using the following to try to calculate the totals, but it doesn't seem to work...

=NZ([ChargesThisStatementPeriod],0)+NZ([CreditsThisStatementPeriod],0)

any suggestions or information would be appreciated.

I hope I have explained it well enough... if anyone has any questions, please feel free to ask...

And once again, thanks everyone for your help...

Dave

 

[statsman]

Just a guess, but try

=Iif([ChargesThisStatementPeriod],0)+Iif([CreditsThisStatementPeriod],0)

Instead of:

=NZ([ChargesThisStatementPeriod],0)+NZ([CreditsThisStatementPeriod],0)

 

[davidg47]

Thanks for your reply...

The new formula succeeded in not putting a value in the calculated field if the CreditsThisStatementPeriod field is zero, which is something I wanted it to do, but it stopped making the calculations.

Man oh man, I had no idea making calculations would be so difficult.

 

[davidg47]

Just a guess, but try

=Iif([ChargesThisStatementPeriod],0)+Iif([CreditsThisStatementPeriod],0)

Instead of:

=NZ([ChargesThisStatementPeriod],0)+NZ([CreditsThisStatementPeriod],0)

I finally got the calculation to work. My original formula was good, it just needed one little change.

If the field is not showing any value, it is not “0”, it is NULL, the value does not have a default “0” assigned to it. So the zero should have been changed to NULL in my formula.

Here is the working formula just so you know what I did:

=NZ([ChargesThisStatementPeriod],Null)-NZ[CreditsThisStatementPeriod],Null)

Thanks for your help and for jogging my brain.

 

[davidg47]

Just to keep anyone that may be following this post informed. Nothing I tried in the report seemed to work perfectly in both versions of the report (Access 2000 and Access 2003). But we finally came upon a solution that works across all versions. We just wrote it into the query that the report is based on. The solution is: chargesthisstatementperiod-creditsthisstatementperiod AS CreditDueAmount. If anyone has any further wisdom on this... PLEASE pass it on.

Thanks to everyone that added their knowledge so far.
Dave