Running Totals Problem

[Dale Clarke]

I need some help with obtaining a total on a form. I am using Access
2000 on a Win2k system.

On a continuous form, I have a text box for Estimated Cost, Actual Cost
and a third for Adjusted Cost in the detail section of the form. The
data source for the form is a query with the Estimated Cost and Actual
Cost being data entry fields on the form. The Adjusted Cost field is
obtained by a calculated field in the query. I need the calculation to
execute only when the value of Actual Cost is greater then 0. I used
the following expression in the calculated field of the query to obtain
my desired result and format the results:

AdjCost:
Format(IIf([ActCost]=0,"",[EstCost]-[ActCost]),"$#,##0.00;($#,##0.00)”)

This seems to work fine when I open the form; the data is there and
formatted correctly. However, I need to be able to sum this field in
the footer section of the form and that is where my problem appears. I
have three text boxes in the form footer, which perform a running total
for each of the three fields in the detail section. My problem is that
I am unable to get the third text box in the footer to sum the Adjusted
Cost total.

For each of the unbound text boxes in the footer, I use the following
formula’s to obtain my running totals:

=Sum([Estimated Cost])
=Sum([Actual Cost])
=Sum([AdjCost])

When I open the form I get #Error in all of the sum text boxes in the
footer section. If I remove the =Sum([AdjCost]) from the control source
of the third text box, the other two text boxes total correctly. I have
checked the spelling in all of the formulas and they are correct. I
believe the problem is with the expression above that I am using in the
calculated field of the query. I believe the formatting is somehow
affecting the Sum function. Is there a more effective way to obtain the
desired results that I need? Any help would be greatly appreciated.

 

[Dale Clarke]

Ami,

Thanks for the help, your solution appears to work perfect.

Dear Dale,

There are four minor mistakes in you case:

A: Skip formating values in the query because that will
change them to text.
B: Use Null instead of "".
C: Use <=0 but not =0 in the constrain because you said
that AdjCost may only be calculated if ActCost is grater
than 0.

Then will you have following in your query:
AdjCost: IIf([ActCost]<=0,Null,[EstCost]-[ActCost])

D: Format the ActCost textbox in the continuous form.

This will work! (I hope).

Enjoy!
Arni Laugdal, MMI

-----Original Message-----
I need some help with obtaining a total on a form. I am using Access
2000 on a Win2k system.

On a continuous form, I have a text box for Estimated Cost, Actual Cost
and a third for Adjusted Cost in the detail section of the form. The
data source for the form is a query with the Estimated Cost and Actual
Cost being data entry fields on the form. The Adjusted Cost field is
obtained by a calculated field in the query. I need the calculation to
execute only when the value of Actual Cost is greater then 0. I used
the following expression in the calculated field of the query to obtain
my desired result and format the results:

AdjCost:
Format(IIf([ActCost]=0,"",[EstCost]-[ActCost]),"$#,##0.00; ($#,##0.00)")

This seems to work fine when I open the form; the data is there and
formatted correctly. However, I need to be able to sum this field in
the footer section of the form and that is where my problem appears. I
have three text boxes in the form footer, which perform a running total
for each of the three fields in the detail section. My problem is that
I am unable to get the third text box in the footer to sum the Adjusted
Cost total.

For each of the unbound text boxes in the footer, I use the following
formula's to obtain my running totals:

=Sum([Estimated Cost])
=Sum([Actual Cost])
=Sum([AdjCost])

When I open the form I get #Error in all of the sum text boxes in the
footer section. If I remove the =Sum([AdjCost]) from the control source
of the third text box, the other two text boxes total correctly. I have
checked the spelling in all of the formulas and they are correct. I
believe the problem is with the expression above that I am using in the
calculated field of the query. I believe the formatting is somehow
affecting the Sum function. Is there a more effective way to obtain the
desired results that I need? Any help would be greatly appreciated.


.