Sum Function Driving me to the brink!

[JonoGee]

Hi. Good Day to you all.
Can someone please help me understand what variable is making this simple Sum Function not work on this Subform. The Field SubTotal is calculated in the Query. It is formatted as Currency. It returns #Error. I have this method working on other SubForms but this one will simply not Playball. I can't seem to track down any rhyme or reason to it.

[Minty]

Please show us the query SQL and what you have set the control source to on the the sub form.

[JonoGee]

SELECT tblQuotationNonSKUs.QuotationNonSKUsID, tblQuotationNonSKUs.QuotationNumberID, tblQuotationNonSKUs.LineDescription, tblQuotationNonSKUs.Qty, tblQuotationNonSKUs.UOMID, tblQuotationNonSKUs.GrossMarginPercent, tblQuotationNonSKUs.SalePriceGBP, tblQuotationNonSKUs.LineTaxGBP, tblQuotationNonSKUs.TaxRateID, tblQuotationNonSKUs.LineNotes1, tblQuotationNonSKUs.LineNotes2, tblQuotationNonSKUs.LineNotes3, tblQuotationNonSKUs.CostPrice, tblQuotationNonSKUs.DiscountPercent, tblQuotationNonSKUs.DiscountGBP, tblTaxRate.TaxRate, [Qty]*[SalePriceGBP] AS SubTotal, IIf([DiscountPercent] Is Not Null,[SubTotal]*[DiscountPercent],IIf([DiscountGBP] Is Not Null,[DiscountGBP],0)) AS [Total Discount], ([SubTotal]-[Total Discount]) AS [Total Ex Tax], [Total Ex Tax]*[TaxRate] AS [Total Tax], [Total Ex Tax]+[Total Tax] AS [Total Inc Tax] FROM tblQuotationNonSKUs LEFT JOIN tblTaxRate ON tblQuotationNonSKUs.TaxRateID = tblTaxRate.TaxRateID;

[Minty]

Okay - if you run that query from the query editor, I assume you get sensible results?
If you do - what is the control source for the form where it is displaying #Error?

[JonoGee]

Yeah the query runs fine and calculates and display all the data as expected fromthe query editor..
I am not sure what it is you want me to show you when you reference the control source? Do you want to see the table design that this SubForm references?

[Minty]

On the form where it says #Error - in design mode what is the control source for that textbox.

I thought the query was the record source for the subform?

[JonoGee]

The query is the record source for the SubForm.

The control source is just the Sum formular referencing the created field in the query called [SubTotal]

[Minty]

It might be because the Subtotal is already a calculated summed field in the underlying query.

Try creating another query that uses the original query as it's source, just bring in all the fields, and save it.
Use that as the basis for the sub form and see if it makes any difference.

[JonoGee]

The SubTotal is Calculated field in the underlying Query. The Sum of SubTotal that is not working is suppose to be summing up the SubTotal from multiples records in the continuous subform. Does that make sense? I will try your suggestion and see if I get any joy.. Theres clearly something interferring with it but I can't figure out for the life of me whats wrong

[CJ_London]

what is the name of your subtotal control in the detail section?

[JonoGee]

It is just 'SubTotal' as defined manually in the query..

[CJ_London]

a few thoughts

1. change the name of the control from subTotal to txtSubotal or similar to avoid confusion with the name

2. appreciate the subtotal value should be numeric - but is it? suggest modify your query to cCur([Qty]*[SalePriceGBP])

3. do you have some errors in you calculation due to missing data?

[mike60smart]

You could also try:-
Sub Total:Nz([Qty],0)*Nz([SalePriceGBP],0)