CODEWITHSUNDEEP
sql-serverms-access
Andrew Henry
Andrew Henry

Reputation: 7

Sum a Calculated Text Box with Criteria

--New Info at Bottom

I have a Invoicing Database I am working on. The invoice has calculated fields which is generated in a form on unbound text boxes. Some products require a recycling fee and I am having trouble getting it to sum the recycling fee.

For example. If a customer orders 1 bucket of paint and 1 paint brush I have to charge the fee to the bucket of paint. Before in the sub form for the products I simply had:

=[QTY]*DLookup("Price","Fees","Name='Paint'")

but now I need to add a criteria requirement where I have a field called Paint (Yes/No Field) and it won't total all of the lines in the sub-form. Here is what my code looks like now:

=IIf([Paint]=True,[QTY],0)*DLookup("Price","Fees","Name='Paint'")

However unlike my code before it won't total all data in the field, only the last one I clicked on.

Any and all help appreciated

New Info: Old formula isn't working as well, only totaling a single line of data where white box has arrow. Possibly something wrong with a changed format? Sub form was setup in Tabular if that somehow changed.

Upvotes: 0

Views: 36

Answers (1)

June7
June7

Reputation: 21370

If you want to total all rows in subform, that is an aggregate calculation. Aggregate calc must be in textbox in form header or footer section and need to use an aggregate function: =Sum(Qty)

=Sum(IIf([Paint]=True,[QTY],0))*DLookup("Price","Fees","Name='Paint'")

However, header and footer won't display when Datasheet view is applied. So a textbox on main form can reference the subform textbox: =[subform container]!textbox

Upvotes: 1

Related Questions