Calculated field disappears...

[rjasong23]

I have a calculated field in a form that calculates the amount due. When I open the form or the record and the record has an amount due of 0, then the field says "$0.00" for a second and then disappears. Any idea why this is so? This becomes a problem because $0.00 doesn't appear on any reports either. Any help would be appreciated!

 

[Pat Hartman]

Formatting should always be done at the last possible place. So, change your report to format the control. If there is a possibility that some values might be null, you'll need to use the Nz() function in your query to eliminate them.

 

[rjasong23]

Is there a way to format it that way before the report?? I would like to be able to see a $0.00 value if there is no balance due when I pull up the record in form view. I tried
Nz(([invoiceamount]-[invoicepayments subform].[Form]![sumofamount]=0,"$0.00",[invoiceamount]-[invoicepayments subform].[Form]![sumofamount])),"$0.00")

And every other combination of Nz() without success for that field.

 

[Pat Hartman]

You don't need any code. Just set the format for the control.

 

[rjasong23]

Ok, maybe I'm just an idiot then - because I have tried every format control I could find to no avail - any suggestions??

 

[mwn1218]

Could try something like this:

Iif( IsNull([FeildName]), True, False)
True = "$0.00"
False = whatever your calculation is

 

[Pat Hartman]

Look up "Format Property - Number and Currency Data Types" in help. The format property for number and currency data types contains FOUR parts - positive, negative, zero, null. Here's an example showing all 4 parts.

$#,##0.00[Green];($#,##0.00)[Red];"Zero";"Null"

The field will look like the following:

Positive:    [COLOR=Green]$0.01[/COLOR]
Negative:   [COLOR=Red]$0.01[/COLOR]
Zero:        Zero
Null:          Null

 

[Rich]

You have to use the HasData method if you're referring to a report, if it's a form you're talking about then your Nz statement is not correct