field data disappears

[miss031]

This is really strange, and I'm sure there is a simple answer. I have a
field on a form that is a calculation of fields on 2 subforms. When the
condition of invoice_ID is not null, data is properly displayed in the
txt_balance_due field,but when the invoice_ID *is* null, the data is
displayed for a split second, and then the field is blank. I have no code in
the form, or any of its subforms, that refreshes or changes the data. I
can't figure it out.

The controlsource of txt_balance_due is:
=IIf(IsNull([txt_invoice_ID]),[frm_sub_bidder_invoices].[Form]![balance_fwd]+[subform_clerking].[Form]![SumOfgrandtotal],[frm_sub_bidder_invoices].[Form]![balance_fwd])

 

[Allen Browne]

Try:
=Nz([frm_sub_bidder_invoices].[Form]![balance_fwd],0)
+ IIf([txt_invoice_ID] Is Null,
Nz([subform_clerking].[Form]![SumOfgrandtotal],0), 0)

The core idea here is that anything plus null is null. Hence the Nz()
converts each half to Null, so the other part still works even if one part
is null.

 

[miss031]

Excellent! Now, any idea why, when my Format property is set to Currency,
and the Decimal is set to 2, that it doesn't round off to 2 decimal places?

Try:
=Nz([frm_sub_bidder_invoices].[Form]![balance_fwd],0)
+ IIf([txt_invoice_ID] Is Null,
Nz([subform_clerking].[Form]![SumOfgrandtotal],0), 0)

The core idea here is that anything plus null is null. Hence the Nz()
converts each half to Null, so the other part still works even if one part
is null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

This is really strange, and I'm sure there is a simple answer. I have a
field on a form that is a calculation of fields on 2 subforms. When the
condition of invoice_ID is not null, data is properly displayed in the
txt_balance_due field,but when the invoice_ID *is* null, the data is
displayed for a split second, and then the field is blank. I have no code
in
the form, or any of its subforms, that refreshes or changes the data. I
can't figure it out.

The controlsource of txt_balance_due is:
=IIf(IsNull([txt_invoice_ID]),[frm_sub_bidder_invoices].[Form]![balance_fwd]+[subform_clerking].[Form]![SumOfgrandtotal],[frm_sub_bidder_invoices].[Form]![balance_fwd])

 

[Allen Browne]

Currency should round to 2 places regardless of the Decimal Places setting
(at least in a dollars and cents country.)

If it is not doing so, Access may be misunderstanding the data type.

You could explicitly do it with by adding Round():
=Round(Nz([frm_sub_bidder_invoices].[Form]![balance_fwd],0)
+ IIf([txt_invoice_ID] Is Null,
Nz([subform_clerking].[Form]![SumOfgrandtotal],0), 0), 2)

 

[miss031]

Thanks, that worked great, but strangely, I thought I would try a test, and
when I changed the control source of the field to another table field, and
then back to the expression, the Currency format came back!

Currency should round to 2 places regardless of the Decimal Places setting
(at least in a dollars and cents country.)

If it is not doing so, Access may be misunderstanding the data type.

You could explicitly do it with by adding Round():
=Round(Nz([frm_sub_bidder_invoices].[Form]![balance_fwd],0)
+ IIf([txt_invoice_ID] Is Null,
Nz([subform_clerking].[Form]![SumOfgrandtotal],0), 0), 2)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Excellent! Now, any idea why, when my Format property is set to Currency,
and the Decimal is set to 2, that it doesn't round off to 2 decimal
places?

 

[Allen Browne]

Hmm. If it's behaving like that, you might want to make sure Name
AutoCorrect is turned off, and then compact/repair the database.

More info:
http://allenbrowne.com/bug-03.html