Problem with calculated field

  • Thread starter Thread starter John
  • Start date Start date
J

John

I am setting up an Invoice report using a query. The query has a calculated
field, "Extended price" (UnitPrice*Quantity). So far so good, I can display
all of this in the detail of the report. I want to put the sum of all the
extended prices in the footer as "Subtotal". The Orders Subform in Northwind
does this simply with =Sum([ExtendedPrice]) but I get #Error if I try this.
How can I achieve this. Any help appreciated.

Thanks

John
 
John said:
I am setting up an Invoice report using a query. The query has a calculated
field, "Extended price" (UnitPrice*Quantity). So far so good, I can display
all of this in the detail of the report. I want to put the sum of all the
extended prices in the footer as "Subtotal". The Orders Subform in Northwind
does this simply with =Sum([ExtendedPrice]) but I get #Error if I try this.
How can I achieve this. Any help appreciated.
Click to expand...

Which footer? Page Header/Footers do not support aggregate functions. Group or
Report Header/Footers should work though.
 
John,

Sum doesn't work with a named calculated field such as Sum([ExtendedPrice]).
Rather you need to put the calculateion inside the function as
Sum([UnitPrice]*[Quantity]).
 
It works ok in the group footer, but that's no good to me. That will give a
subtotal for each group of data on the report, but what I need is a subtotal
of all the exteded prices in the report.

John


Rick Brandt said:
John said:
I am setting up an Invoice report using a query. The query has a calculated
field, "Extended price" (UnitPrice*Quantity). So far so good, I can display
all of this in the detail of the report. I want to put the sum of all the
extended prices in the footer as "Subtotal". The Orders Subform in Northwind
does this simply with =Sum([ExtendedPrice]) but I get #Error if I try this.
How can I achieve this. Any help appreciated.
Click to expand...

Which footer? Page Header/Footers do not support aggregate functions. Group or
Report Header/Footers should work though.
Click to expand...
 
Tried this and it works in the group footer but not in page footer

John


PC Datasheet said:
John,

Sum doesn't work with a named calculated field such as Sum([ExtendedPrice]).
Rather you need to put the calculateion inside the function as
Sum([UnitPrice]*[Quantity]).

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


John said:
I am setting up an Invoice report using a query. The query has a calculated
field, "Extended price" (UnitPrice*Quantity). So far so good, I can display
all of this in the detail of the report. I want to put the sum of all the
extended prices in the footer as "Subtotal". The Orders Subform in Northwind
does this simply with =Sum([ExtendedPrice]) but I get #Error if I try this.
How can I achieve this. Any help appreciated.

Thanks

John
Click to expand...
Click to expand...
 
Tried this and it works in the group footer but not in page footer

John

PC Datasheet said:
John,

Sum doesn't work with a named calculated field such as Sum([ExtendedPrice]).
Rather you need to put the calculateion inside the function as
Sum([UnitPrice]*[Quantity]).

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


John said:
I am setting up an Invoice report using a query. The query has a calculated
field, "Extended price" (UnitPrice*Quantity). So far so good, I can display
all of this in the detail of the report. I want to put the sum of all the
extended prices in the footer as "Subtotal". The Orders Subform in Northwind
does this simply with =Sum([ExtendedPrice]) but I get #Error if I try this.
How can I achieve this. Any help appreciated.

Thanks

John
Click to expand...
Click to expand...
Click to expand...

John,
In his first reply to your original post Rich Brandt told you you
cannot use this expression in a Page Footer!!

From his reply:
Which footer? Page Header/Footers do not support aggregate functions.
Group or Report Header/Footers should work though.

If you wish to show sums (even Sums of Sums) in the Page Footer, read
Microsoft KnowledgeBase article:
132017 'How to sum a column of numbers in a report by page.'
 
Back
Top