field data disappears

  • Thread starter Thread starter miss031
  • Start date Start date
M

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])
 
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.
 
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 said:
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.

miss031 said:
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])
Click to expand...
Click to expand...
 
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)
 
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!

Allen Browne said:
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.

miss031 said:
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?
Click to expand...
Click to expand...
 
Back
Top