Sum calculated form fields that use dlookup

[emtenzer]

I have a form that is keeping track of miscellaneous sales. The items first have to look up the cost of an item, then find out of there is sales tax on that item and add to it. So I have:

=([Quantity]*ItemKey.Column(2)) which is the cost of the item

=IIf(IsNull([ItemKey]),0,DLookUp("TaxRate","tblMiscellaneousItemsforSale","ItemID = " & [ItemKey])*[txtTotal]) which looks up if it is a taxable item

=[Quantity]*[ItemKey].[Column](4) which is the total of that item

I need to sum all items though and I can only get #Error. I am putting that text box in the form footer.

Any ideas? Thank you in advance.

 

[boblarson]

What is the exact control source you are using in the text box on the form footer?

Bob Larson
FORMER Microsoft Access MVP (2008-2009, 2009-2010)
Free Tutorials/Samples/Tools:

http://www.btabdevelopment.com

 

[emtenzer]

Honestly, I've tried about everything. I've created extra text boxes to hold the values of the calculations. I've tried combining every single calculation with "Sum(...) What I have right now is: Sum([Quantity]*[ItemKey].[Column](4)).I'm currently looking into trying to make this form a split form and adding a totals row.

 

[MajP]

FYI you can not use the sum function on calculated controls.