=Sum([Cost]) gives #Error

[Stuart Grant]

I have a form with the style Continuous Form which shows the Cost and
Last Value of the items. There are only 8 at the present stage. In the
footer I set two text boxes TotalCost and TotalLastVal. I set the
Control Source of the TotalCost Box to =Sum([Cost]) but get #Error when
I open the form. Similarly the TotalLastVal text box.

I have checked over and over for typos or mistakes in the name of the
field without success.

What can be wrong ?

Stuart

 

[Wolfgang Kais]

Hello Stuart.

I have a form with the style Continuous Form which shows the Cost
and Last Value of the items. There are only 8 at the present stage.
In the footer I set two text boxes TotalCost and TotalLastVal.

In what footer? It should be the form footer, not the page footer.

I set the Control Source of the TotalCost Box to =Sum([Cost]) but
get #Error when I open the form. Similarly the TotalLastVal text
box.

The cost and last value must not be textboxes with calculated values.
If they are calculated values, you have to repeat the formula in the
sum formula. For example, if cost = [unitprice]*[quantity] where
unitprice and quantity come from the underlying table/query, then
TotalCost = Sum([unitprice]*[quantity])

 

[Stuart Grant]

Hello Stuart.

I have a form with the style Continuous Form which shows the Cost
and Last Value of the items. There are only 8 at the present stage.
In the footer I set two text boxes TotalCost and TotalLastVal.

In what footer? It should be the form footer, not the page footer.

I set the Control Source of the TotalCost Box to =Sum([Cost]) but
get #Error when I open the form. Similarly the TotalLastVal text
box.

The cost and last value must not be textboxes with calculated values.
If they are calculated values, you have to repeat the formula in the
sum formula. For example, if cost = [unitprice]*[quantity] where
unitprice and quantity come from the underlying table/query, then
TotalCost = Sum([unitprice]*[quantity])

Wolfgang

Thanks for replying.

The two text boxes are in the Form Footer.
Cost and Last Value are not calculated and are correctly shown in the
Detail section.

Stuart

 

[Wolfgang Kais]

Hello Stuart.

The two text boxes are in the Form Footer.

I set the Control Source of the TotalCost Box to =Sum([Cost]) but
get #Error when I open the form. Similarly the TotalLastVal text
box.

The cost and last value must not be textboxes with calculated values.
If they are calculated values, you have to repeat the formula in the
sum formula. For example, if cost = [unitprice]*[quantity] where
unitprice and quantity come from the underlying table/query, then
TotalCost = Sum([unitprice]*[quantity])

Cost and Last Value are not calculated and are correctly shown in the
Detail section.

These must be the names of fields from the recordseource of the form.
If this should all be correct: Open the VB Editor and check the
references under Tools|References. If there should be a recerence that
starts with "MISSING:", either delete it (if it's no longer needed) or
replace it with the correct one. For example, if there's a missing
("broken") reference to the Office 12.0 Library but you have installed
Office 2003 on your PC, replace it with a reference to the Office 11.0
Library.

 

[Stuart Grant]

Hello Stuart.

I have a form with the style Continuous Form which shows the Cost
and Last Value of the items. There are only 8 at the present stage.
In the footer I set two text boxes TotalCost and TotalLastVal.

In what footer? It should be the form footer, not the page footer.

I set the Control Source of the TotalCost Box to =Sum([Cost]) but
get #Error when I open the form. Similarly the TotalLastVal text
box.

The cost and last value must not be textboxes with calculated values.
If they are calculated values, you have to repeat the formula in the
sum formula. For example, if cost = [unitprice]*[quantity] where
unitprice and quantity come from the underlying table/query, then
TotalCost = Sum([unitprice]*[quantity])

Wolfgang

Thanks for replying.

The two text boxes are in the Form Footer.
Cost and Last Value are not calculated and are correctly shown in the
Detail section.

Stuart

 

[Stuart Grant]

These must be the names of fields from the recordseource of the form.
If this should all be correct: Open the VB Editor and check the
references under Tools|References. If there should be a recerence that
starts with "MISSING:", either delete it (if it's no longer needed) or
replace it with the correct one. For example, if there's a missing
("broken") reference to the Office 12.0 Library but you have installed
Office 2003 on your PC, replace it with a reference to the Office 11.0
Library.

Thanks again Wolfgang

They are all fields in the record source of the form.

Under Tools¦References there is no reference that starts with "Missing".
I have only a few references checked but they include Microsoft Access
Library 11.0. I have not made any changes to the references in months.

Stuart

 

[Wolfgang Kais]

Hello Stuart.

Thanks again Wolfgang

They are all fields in the record source of the form.

Under Tools¦References there is no reference that starts with "Missing".
I have only a few references checked but they include Microsoft Access
Library 11.0. I have not made any changes to the references in months.

You should verify that the field Cost is a field in the recordsource of
the form (or does it appear in a subform)?
To do so, click the drop down button in the controlsource property of
the textbox TotalCost in the properties window. If a textbox with the
same name exists, rename it to txtCost.
If all appears correct, verify the datatype of the cost field. Is it a
data type that allows calculations like a sum? Have you created you own
function named "sum" in VBA? Repair the database.
If all this didn't help: I have no other idea.

 

[Stuart Grant]

You should verify that the field Cost is a field in the recordsource of
the form (or does it appear in a subform)?
To do so, click the drop down button in the controlsource property of
the textbox TotalCost in the properties window. If a textbox with the
same name exists, rename it to txtCost.
If all appears correct, verify the datatype of the cost field. Is it a
data type that allows calculations like a sum? Have you created you own
function named "sum" in VBA? Repair the database.
If all this didn't help: I have no other idea.

Hello Wolfgang

I am very sorry for the delay in replying. I lost my Internet
connection for three days but all now in order.

The field Cost does is a field in the query which is the record source
for the form and it came into the query from the a table. It is not a
calculated field. There are no subforms in the database. I haven't
created my own funstion "Sum". I haven't done a repair but everything
else works. I'm baffled.

I appreciate your patience and your willingness to help but it looks
like the end of the road. I'll keep trying to check everything again.

Stuart