=Sum([Cost]) gives #Error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Stuart Grant - 06 May 2008 14:38 GMT

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

Reply to this Message

Wolfgang Kais - 06 May 2008 20:21 GMT

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])

Signature

Regards,
Wolfgang

Reply to this Message

Stuart Grant - 07 May 2008 08:15 GMT

> Hello Stuart.
>
[quoted text clipped - 13 lines]
> 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

Reply to this Message

Wolfgang Kais - 07 May 2008 13:04 GMT

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.

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

Signature

Regards,
Wolfgang

Reply to this Message

Stuart Grant - 08 May 2008 09:00 GMT

> 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
[quoted text clipped - 4 lines]
> 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

Reply to this Message

Wolfgang Kais - 08 May 2008 11:02 GMT

Hello Stuart.

>> 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
[quoted text clipped - 4 lines]
>> Office 2003 on your PC, replace it with a reference to the Office 11.0
>> Library.

> Thanks again Wolfgang
>
[quoted text clipped - 3 lines]
> 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.

Signature

Regards,
Wolfgang

Reply to this Message

Stuart Grant - 12 May 2008 08:15 GMT

> You should verify that the field Cost is a field in the recordsource of
> the form (or does it appear in a subform)?
[quoted text clipped - 5 lines]
> 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

Reply to this Message

Stuart Grant - 07 May 2008 13:06 GMT

>> Hello Stuart.
>>
[quoted text clipped - 23 lines]
>
> Stuart

Reply to this Message