I want report field to display 00's if field is zero or Null (1 Viewer)

[atrium]

1. I have a report with columns of Currency figures I have 2 that I wont to show 0.00 if they are zero of Null. These fields are calculated (Sum) fields in the feeding query

When I run the report The TotInterest and the Totpayments don't have a zero ($0.00) in their cell.

2. To calculate the Balance file i use

Balance: IIf(([MainDebt]+Nz([TotInterest]))-Nz([TotPayments])>0,([MainDebt]+Nz([TotInterest]))-Nz([TotPayments]),0)

But it's not accommodating where the calculation falls into negative. Where the MainDebt is less than the totPayments the balance should be negative

3. Also you will notice I have a P/Debt or a J/Debt string field. Each row should have one or the other.

3. Also you will notice I have a P/Debt or a J/Debt string field. Each row should have one or the other. It's displaying the string intermittently. The image is a bit of the report on the next page

Can anyone help me please with the above three problems I have. I would be much appreciative.

 

[June7]

1. Format textbox on report to display 0 when null.
Format property: ,,,0
Decimal places property: 2

2. Expression returns 0 if condition returns 0 or less. Why using an IIf()?

3. Suggest you provide db for analysis. Follow instructions at bottom of my post.

 

[atrium]

Thanks June 7. No. 2 is fine now
No.1 - How do I format (I Know a dumb question) outside of the normal drop down options on the control field

 

[June7]

My memory was faulty. Type this in the Format property: #;;;0
Review https://docs.microsoft.com/en-us/office/vba/api/access.format.propertynumber.and.currency

 

[Gasman]

Why do you need the IIF()?
Just sum the values, whether the result is negative, positive or zero?

Re your string problem, we would have to see how you calculate that.