Datasheet subform total

[Theresa]

Hi:

I am working in Access 2007. I have a subform in datasheet view where I am
calculating an "Annual Cost". I added an unbound textbox in my form footer
named "Subtotal" and set the control source to be +Sum([Annual Cost]). I get
and #Error result.

Any suggestions?

 

[Theresa]

Hi:

Yes....sorry for the typo.

The control source of Annual Cost =[Total
Watts]*[Annual_Operating_Hours]*[Rate KWH]/1000

I set the control source of Subtotal to be =Sum([Total
Watts]*[Annual_Operating_Hours]*[Rate KWH]/1000)

but still get the #Error.

Hi:

I am working in Access 2007. I have a subform in datasheet view where I
am
calculating an "Annual Cost". I added an unbound textbox in my form
footer
named "Subtotal" and set the control source to be +Sum([Annual Cost]). I
get
and #Error result.

I'm going to assume that your controlsource is actually "=Sum([Annual
Cost])", and that the plus sign you posted was just a typo. Otherwise, of
course, you need to fix that.

Is [Annual Cost] a calculated control on your subform, or is it a calculated
field in the recordsource? If it's a calculated control, with its
controlsource set to an expression, the the controlsource of your Subtotal
text box must repeat the expression as the argument to the Sum function,
rather than just summing the control.

For example, if the controlsource of [Annual Cost] were this:

=[Cost1]+[Cost2]

.... then the controlsource of Subtotal would have to be this:

=Sum([Cost1]+[Cost2])

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Hi:

Yes....sorry for the typo.

The control source of Annual Cost =[Total
Watts]*[Annual_Operating_Hours]*[Rate KWH]/1000

I set the control source of Subtotal to be =Sum([Total
Watts]*[Annual_Operating_Hours]*[Rate KWH]/1000)

but still get the #Error.

You said the Subtotal text box was in the form footer. Is that the form
footer of the subform, or of the main form?

Are [Total Watts], [Annual_Operating_Hours], and [Rate KWH] all fields in
the subform's recordsource?

 

[Theresa]

The Subtotal text box is in the footer of the subform. I do have a textbox
on the main form that will reference the textbox on the subform to show the
subtotal on the main form. The control source on the main form is currently
blank.

The control source for Total Watts =[Fixture_Qty]*[Watts]

Annual Operating Hours is a lookup field to the operating hours table

Rate KWH is a manually entered field.

You said the Subtotal text box was in the form footer. Is that the form

footer of the subform, or of the main form?

Are [Total Watts], [Annual_Operating_Hours], and [Rate KWH] all fields in
the subform's recordsource?

Hi:

Yes....sorry for the typo.

The control source of Annual Cost =[Total
Watts]*[Annual_Operating_Hours]*[Rate KWH]/1000

I set the control source of Subtotal to be =Sum([Total
Watts]*[Annual_Operating_Hours]*[Rate KWH]/1000)

but still get the #Error.

You said the Subtotal text box was in the form footer. Is that the form
footer of the subform, or of the main form?

Are [Total Watts], [Annual_Operating_Hours], and [Rate KWH] all fields in
the subform's recordsource?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Theresa]

I've tried the following:

=Sum(([Watts]*[Fixture_Qty])*[Annual_Operating_Hours]*[Rate KWH]/1000)

but still get the error. If I remove the sum function I get the annual cost
for the first record only.

Hi:

Yes....sorry for the typo.

The control source of Annual Cost =[Total
Watts]*[Annual_Operating_Hours]*[Rate KWH]/1000

I set the control source of Subtotal to be =Sum([Total
Watts]*[Annual_Operating_Hours]*[Rate KWH]/1000)

but still get the #Error.

You said the Subtotal text box was in the form footer. Is that the form
footer of the subform, or of the main form?

Are [Total Watts], [Annual_Operating_Hours], and [Rate KWH] all fields in
the subform's recordsource?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

The Subtotal text box is in the footer of the subform.

Okay, fine.

The control source for Total Watts =[Fixture_Qty]*[Watts]

Ah , so following the rule I mentioned earlier, at the very least you're
going to have to change the controlsource of Subtotal to:

=Sum([Fixture_Qty]*[Watts]*[Annual_Operating_Hours]*[Rate KWH]/1000)

The Sum() expression can't refer to controls on the form, only to fields in
the form's recordsource.

But the above revised controlsource may still not work, because of these
things you mentioned:

Annual Operating Hours is a lookup field to the operating hours table
Rate KWH is a manually entered field.

In both cases, I'm not sure what you mean. When you say "Annual Operating
Hours is a lookup field", what do you mean? Is it in the form's (subform's)
recordsource, or are you saying that it's a calculated control whose
controlsource is, maybe, a DLookup() expression?

When you say "Rate KWH is a manually entered field", do you mean that it's
an unbound text box on the form, and thus not in the form's recordsource?

 

[Theresa]

Annual Operating hours is a field in the forms record source, as is Rate KWH

The Subtotal text box is in the footer of the subform.

Okay, fine.

The control source for Total Watts =[Fixture_Qty]*[Watts]

Ah , so following the rule I mentioned earlier, at the very least you're
going to have to change the controlsource of Subtotal to:

=Sum([Fixture_Qty]*[Watts]*[Annual_Operating_Hours]*[Rate KWH]/1000)

The Sum() expression can't refer to controls on the form, only to fields in
the form's recordsource.

But the above revised controlsource may still not work, because of these
things you mentioned:

Annual Operating Hours is a lookup field to the operating hours table
Rate KWH is a manually entered field.

In both cases, I'm not sure what you mean. When you say "Annual Operating
Hours is a lookup field", what do you mean? Is it in the form's (subform's)
recordsource, or are you saying that it's a calculated control whose
controlsource is, maybe, a DLookup() expression?

When you say "Rate KWH is a manually entered field", do you mean that it's
an unbound text box on the form, and thus not in the form's recordsource?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Annual Operating hours is a field in the forms record source, as is Rate
KWH

Then in what sense is Annual Operating Hours a lookup field?

Would you mind posting the recordsource of this subform? That will help me
understand. If it's a table, please post the fields in the table. If it's
a query, please post the SQL of the query.

 

[Theresa]

Sorry.....I should have said:

Annual Operating hours is pulled from a query behind the subforms recordsource

In other words, the source for the subform is a query using the Electrical
Costs table and the Operating hours table

The Subtotal text box is in the footer of the subform.

Okay, fine.

The control source for Total Watts =[Fixture_Qty]*[Watts]

Ah , so following the rule I mentioned earlier, at the very least you're
going to have to change the controlsource of Subtotal to:

=Sum([Fixture_Qty]*[Watts]*[Annual_Operating_Hours]*[Rate KWH]/1000)

The Sum() expression can't refer to controls on the form, only to fields in
the form's recordsource.

But the above revised controlsource may still not work, because of these
things you mentioned:

Annual Operating Hours is a lookup field to the operating hours table
Rate KWH is a manually entered field.

In both cases, I'm not sure what you mean. When you say "Annual Operating
Hours is a lookup field", what do you mean? Is it in the form's (subform's)
recordsource, or are you saying that it's a calculated control whose
controlsource is, maybe, a DLookup() expression?

When you say "Rate KWH is a manually entered field", do you mean that it's
an unbound text box on the form, and thus not in the form's recordsource?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Theresa]

SELECT Electrical_Costs_tbl.*,
Annual_Operating_Hours_tbl.Annual_Operating_Hours
FROM Annual_Operating_Hours_tbl RIGHT JOIN Electrical_Costs_tbl ON
Annual_Operating_Hours_tbl.[Annual_Operating_Hours ID] =
Electrical_Costs_tbl.Operating_Hours;

 

[Dirk Goldgar]

SELECT Electrical_Costs_tbl.*,
Annual_Operating_Hours_tbl.Annual_Operating_Hours
FROM Annual_Operating_Hours_tbl RIGHT JOIN Electrical_Costs_tbl ON
Annual_Operating_Hours_tbl.[Annual_Operating_Hours ID] =
Electrical_Costs_tbl.Operating_Hours;

Hmm, well, that doesn't list the fields in Electrical_Costs_tbl, but I can
see that the field is named "Annual_Operating_Hours", not "Annual Operating
Hours", which is what you used in your expression. You'd better check all
the field names. Remember, your Sum() expression must use the field names
in the recordsource query, which are not necessarily the same names as those
of the controls on your form.

 

[Theresa]

Hi Jeanette:

This doesn't apply for me. I am getting the error whether there are records
or not. If I set the control source of the unbound textbox in my subform
footer to be:

=[Fixture_Qty]*[Watts]*[Annual_Operating_Hours_ID].[Column](1)*[Rate KWH]/1000

The calculation occurs correctly. As soon as I add the sum function:

=Sum([Fixture_Qty]*[Watts]*[Annual_Operating_Hours_ID].[Column](1)*[Rate
KWH]/1000)

I get the #Error.

Any suggestions?

Hi Theresa,
here is the code you need to avoid the error.

http://allenbrowne.com/RecordCountError.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi:

I am working in Access 2007. I have a subform in datasheet view where I
am
calculating an "Annual Cost". I added an unbound textbox in my form
footer
named "Subtotal" and set the control source to be +Sum([Annual Cost]). I
get
and #Error result.

Any suggestions?

 

[Dirk Goldgar]

Hi Jeanette:

This doesn't apply for me. I am getting the error whether there are
records
or not. If I set the control source of the unbound textbox in my subform
footer to be:

=[Fixture_Qty]*[Watts]*[Annual_Operating_Hours_ID].[Column](1)*[Rate
KWH]/1000

The calculation occurs correctly. As soon as I add the sum function:

=Sum([Fixture_Qty]*[Watts]*[Annual_Operating_Hours_ID].[Column](1)*[Rate
KWH]/1000)

I get the #Error.

Any suggestions?

This is one of those cases where the distinction between a field ( in a
table or query) and a control (on a form or report) becomes very important.
The Sum function is applied to the form's recordsource, and so can only use
the names of fields in that recordsource, not controls on the form or their
properties. In this case, it seems that you are trying to refer to the
Column property of a combo or list box -- a control, not a field. You can't
do that with the Sum function.

You can probably amend your form's recordsource query to include whatever
data is in that column of the combo/list box. Then you could use the Sum
function successfully against expressions involving that field.

 

[Theresa]

Thanks.......I ended up creating some of my caluclations in the query itself,
which then enabled me to use those fields to calculate the subtotals.

Thanks for all the help. It is greatly appreciated.

Hi Jeanette:

This doesn't apply for me. I am getting the error whether there are
records
or not. If I set the control source of the unbound textbox in my subform
footer to be:

=[Fixture_Qty]*[Watts]*[Annual_Operating_Hours_ID].[Column](1)*[Rate
KWH]/1000

The calculation occurs correctly. As soon as I add the sum function:

=Sum([Fixture_Qty]*[Watts]*[Annual_Operating_Hours_ID].[Column](1)*[Rate
KWH]/1000)

I get the #Error.

Any suggestions?

This is one of those cases where the distinction between a field ( in a
table or query) and a control (on a form or report) becomes very important.
The Sum function is applied to the form's recordsource, and so can only use
the names of fields in that recordsource, not controls on the form or their
properties. In this case, it seems that you are trying to refer to the
Column property of a combo or list box -- a control, not a field. You can't
do that with the Sum function.

You can probably amend your form's recordsource query to include whatever
data is in that column of the combo/list box. Then you could use the Sum
function successfully against expressions involving that field.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)