Calculated fields should have zero and not be blank

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
Duane said:
You can show expenditures as 0 by using:
=IIf(sfrmExpenditure.Form.HasData, sfrmExpenditure.Form.txtTotExp, 0)
Click to expand...


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

=IIf(IsError(sfrmExpenditure.Form.txtTotExp), 0,
sfrmExpenditure.Form.txtTotExp)
 
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"
 
thanks for your support.

I have tried out your suggested but it has still not solved the problem.
nothing has changed.
 
Alylia said:
thanks for your support.

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


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?
 
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.

Alylia said:
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 said:
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.
Click to expand...
Click to expand...
 
Thank you so much. I have been struggeling with a problem similar to these
and now your advice fixed it.
 
Back
Top