Calculated fields should have zero and not be blank

[Guest]

I have a calculated field called "Balance" which is the difference between
"Amount Available" and "Total Expenditure" on my main form. Please note that
"Total Expenditure" is calculated from all expenditures on the sub form. I
have noticed that whenever I navigate to records on the main form which have
no expenditures on my sub form, "Total Expenditure" and "Balance" are blank.
I would like them to show zero.

Can you also advise me on this.

Thanks

 

[Guest]

use nz([Total Expenditure],0)-nz([Amount Available],0)

Refer to:
http://allenbrowne.com/casu-12.html
for more info on null values

 

[Duane Hookom]

You can show expenditures as 0 by using:
=IIf(sfrmExpenditure.Form.HasData, sfrmExpenditure.Form.txtTotExp, 0)
If you can't figure this out, then come back with some subform and control
names.

 

[Marshall Barton]

You can show expenditures as 0 by using:
=IIf(sfrmExpenditure.Form.HasData, sfrmExpenditure.Form.txtTotExp, 0)

Duane, HasDate only works in reports. In a form, try this
instead:

=IIf(IsError(sfrmExpenditure.Form.txtTotExp), 0,
sfrmExpenditure.Form.txtTotExp)

 

[Guest]

I have tried your suggestion as you can see from the syntax below but i get
the "#Name?"

=IIf([Exp Subform].Form.HasData,[Exp Subform].Form.txtSumExp,0)

As requested, please find below the name of the subform and control

sub form = "Exp Subform"
text box for total expenditure on sub form has the name "txtSumExp"

 

[Duane Hookom]

Thanks for the correction Marsh. I gotta get outta reports more often.

 

[Guest]

thanks for your support.

I have tried out your suggested but it has still not solved the problem.
nothing has changed.

 

[Marshall Barton]

thanks for your support.

I have tried out your suggested but it has still not solved the problem.
nothing has changed.

Are you getting #Error on the main form'sTotal Expenditure
text box? Or is it just "blank"? I would expect the
former, but earlier you said the latter.

Since this is a fairly common situation with a well known
solution, I am beginning to suspect that there is something
different about what you are doing.

What is the name of the text box on the subform that has the
=Sum(. . .) expression?
Make sure you can see the subform's total text box and see
what it displays when there is no data.

What is the name of the subform ***control*** on the main
form?

 

[Guest]

I still believe that your problem is in your query. If you use the nz
function for your calculations in the query, everything should come over just
fine on your form because you no longer have null values for calculations on
your form.
If you are using the forms footer to sum your data, if there are no null
values, it should some just fine and result in zeros instead of nothing.

I have tried your suggestion as you can see from the syntax below but i get
the "#Name?"

=IIf([Exp Subform].Form.HasData,[Exp Subform].Form.txtSumExp,0)

As requested, please find below the name of the subform and control

sub form = "Exp Subform"
text box for total expenditure on sub form has the name "txtSumExp"

You can show expenditures as 0 by using:
=IIf(sfrmExpenditure.Form.HasData, sfrmExpenditure.Form.txtTotExp, 0)
If you can't figure this out, then come back with some subform and control
names.

 

[Guest]

Thank you so much. I have been struggeling with a problem similar to these
and now your advice fixed it.