sum fields on form footers.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dear Sir/Madame,

When the data source of a form is a more complex query with groups and sums
a sum field at the footer of the form returns the error #Name?. I tried it
with simple queries and it worked but with more complex it does not. I also
tried to put the complex query as a subquery on another query but it still
did not work. Any ideas please

P. Scaram
 
P. Scaram,

It is unlikely that the problem has anything to do with the complexity
of the query. It is more likely to be caused by a spelling error in the
expression in your calculated control, or by the calculated control
having the same name as a field in the form's record source. If you
post back with the SQL view of the troublesome query, and some more
specific details (with examples) of your totals calculations, someone
may be able to help further.
 
Thank you for your help.

The sql query is

SELECT Cust_Order_Dtl.COrderNo, Prov_Order_Dtl.POrderNo,
Sum(Prov_Order_Dtl.PLinePrice) AS sumpercorder, CostProvSum.SumPLinePrice,
CostProvSum.Currency, CostProvSum.PExchangeRate, ProvOrderCost.CostOrder AS
CostOrder, (Sum([PLinePrice])/[SumPLinePrice])*[CostOrder] AS CustCostShare
FROM ((Prov_Order_Dtl INNER JOIN CostProvSum ON Prov_Order_Dtl.POrderNo =
CostProvSum.POrderNo) LEFT JOIN ProvOrderCost ON CostProvSum.POrderNo =
ProvOrderCost.POrderNo) INNER JOIN Cust_Order_Dtl ON (Prov_Order_Dtl.POrderNo
= Cust_Order_Dtl.POrderNo) AND (Prov_Order_Dtl.POrderLineNo =
Cust_Order_Dtl.POrderLineNo)
GROUP BY Cust_Order_Dtl.COrderNo, Prov_Order_Dtl.POrderNo,
CostProvSum.SumPLinePrice, CostProvSum.Currency, CostProvSum.PExchangeRate,
ProvOrderCost.CostOrder;

The fields we want to do a sum on are the CostOrder and the CustCostShare.
They are both defined with aliases. Also the #name? appears after the form
has scrolled down through all the data. If you make a spelling mistake i
noticed it appears immediately on the form.

Please notice that the query has a left join.

On the form the fields are CostOrder and CustCostShare.

In the form footer in the control source they are defined as =
Sum([CostOrder]),
=Sum([CustCostShare]).

Also the "Table" CostProvSum is a sum group query on a detail table.

Thank you in advance,

P. Scaram
 
P. Scaram,

Thanks for the further explanation.

I'm sorry, I can see no reason for the error. You should be able to do
what you are doing. As far as I can see, the problem is not related to
the query, and the degree of complexity of the query is not relevant.
Although it does seem strange to have a field in the query with an alias
(CostOrder) the same as the name of the field it refers to. If you open
the datasheet of the query, does it display all records correctly, as
expected? And the same on the form? No blanks? Regarding: "the #name?
appears after the form has scrolled down through all the data", what is
shown in the totals textboxes prior to the #Name? coming up? Also, can
you let me know the names of the textboxes that the Sum expressions are
in? And the names of the textboxes in the detail of the form which show
the CostOrder and CustCostShare? I feel the problem is more likely to
be related to the form design than to the data.
 
I worked it out in the end. along side the two sum fields I mentioned
=sum(CostOrder]) and = sum([CustCostShare]) I also had a sum on an unbound
field
"CustCost" with data =CostOrder * CustCostShare. The sum field
=sum([CustCost]) or =sum([CostOrder * CustCostShare]) does return an error
Name? and it affects the other sum fields in the form footer. Once I removed
that sum field on the unbound field the other sums worked.

Why though is the sum field on an unbound field not accepted ?

Thank you in advance P. Scara,

Steve Schapel said:
P. Scaram,

Thanks for the further explanation.

I'm sorry, I can see no reason for the error. You should be able to do
what you are doing. As far as I can see, the problem is not related to
the query, and the degree of complexity of the query is not relevant.
Although it does seem strange to have a field in the query with an alias
(CostOrder) the same as the name of the field it refers to. If you open
the datasheet of the query, does it display all records correctly, as
expected? And the same on the form? No blanks? Regarding: "the #name?
appears after the form has scrolled down through all the data", what is
shown in the totals textboxes prior to the #Name? coming up? Also, can
you let me know the names of the textboxes that the Sum expressions are
in? And the names of the textboxes in the detail of the form which show
the CostOrder and CustCostShare? I feel the problem is more likely to
be related to the form design than to the data.

--
Steve Schapel, Microsoft Access MVP


P. Scaram said:
Thank you for your help.

The sql query is

SELECT Cust_Order_Dtl.COrderNo, Prov_Order_Dtl.POrderNo,
Sum(Prov_Order_Dtl.PLinePrice) AS sumpercorder, CostProvSum.SumPLinePrice,
CostProvSum.Currency, CostProvSum.PExchangeRate, ProvOrderCost.CostOrder AS
CostOrder, (Sum([PLinePrice])/[SumPLinePrice])*[CostOrder] AS CustCostShare
FROM ((Prov_Order_Dtl INNER JOIN CostProvSum ON Prov_Order_Dtl.POrderNo =
CostProvSum.POrderNo) LEFT JOIN ProvOrderCost ON CostProvSum.POrderNo =
ProvOrderCost.POrderNo) INNER JOIN Cust_Order_Dtl ON (Prov_Order_Dtl.POrderNo
= Cust_Order_Dtl.POrderNo) AND (Prov_Order_Dtl.POrderLineNo =
Cust_Order_Dtl.POrderLineNo)
GROUP BY Cust_Order_Dtl.COrderNo, Prov_Order_Dtl.POrderNo,
CostProvSum.SumPLinePrice, CostProvSum.Currency, CostProvSum.PExchangeRate,
ProvOrderCost.CostOrder;

The fields we want to do a sum on are the CostOrder and the CustCostShare.
They are both defined with aliases. Also the #name? appears after the form
has scrolled down through all the data. If you make a spelling mistake i
noticed it appears immediately on the form.

Please notice that the query has a left join.

On the form the fields are CostOrder and CustCostShare.

In the form footer in the control source they are defined as =
Sum([CostOrder]),
=Sum([CustCostShare]).

Also the "Table" CostProvSum is a sum group query on a detail table.

Thank you in advance,

P. Scaram
Click to expand...
Click to expand...
 
P. Scaram,

I see that I should have corrected your use of the word "field" right
from the beginning! Forms don't have fields. They have controls. You
can do calculations based on field values. But =Sum([CustCost]) will
not work, because [CustCost] is not a field. You call it an "unbound
field", but it isn't, it's a textbox. Therefore the calculation has to
be done based on fields. Your idea of =sum([CostOrder * CustCostShare])
is a step in the right direction, but the syntax is wrong. I would
expect this to work ok...
=Sum([CostOrder]*[CustCostShare])
 
Back
Top