MS Access Form, Sum based on criteria

You can use DSum. The syntax is fairly straightforward:

DSum("FieldToSum", "Table", "Criteria")

So perhaps your first one would be:

= DSum("Cost", "YourTable", "Class='Equipment' or Class='Carts' or Class='other'")

Hi, I'm getting an error.  Attached are screen prints.

I don't see your screenprints - try again?

You can just copy and paste your code rather than providing a screen print of an error.

I played around with it and got rid of the error.  Now it is only picking up equipment and not carts.  The 2nd issue is I hardcoded the Quote No '2843v4' to get it to total only the items for that quote, but I want it to pick up the quote no on the form

=DSum("[item_total]","tbl_Quote_Equipment","[QuoteNo]= '2843v4' and [Class] = 'Equipment' or [Class]= 'Carts'")

So, add a text box (or combo box) on the form called (for example) tbQuoteNo. Then, change the code to read:

=DSum("[item_total]","tbl_Quote_Equipment","[QuoteNo]= '" & me.tbQuoteNo.Text & "' and ([Class] = 'Equipment' or [Class]= 'Carts')")

Select allOpen in new window

Note that the punctuation before me.tbQuoteNo.Text is '" = single quote (to be included in the criteria string) followed by double quote (delimiting the first part of the criteria string) - the punctuation after is double quote-single quote.

Note that I also added some parentheses around the Class= phrases - I assume you want anything on the quote that's in one of the 2 classes, rather than everything in the quote that is equipment and anything in ANY QUOTE that is Carts - need to be careful of your order when you combine AND and OR.

I tried that and receive an error:  #Name?

I created a text box named tbQuote and here is the dsum formula:
=DSum("[item_total]","tbl_Quote_Equipment","[QuoteNo]= '" & [me].[tbQuoteNo].[Text] & "' and [Class] = 'Equipment' or [Class]= 'Carts'")

Oh - this is not in VBA, is it? Just get rid of the [me]. - that's only used in VBA code to let the VBA editor list all of the stuff on your form. If you're just coding the control source for a control on the form, you can't use "me" - you can use the Builder dialog, which also lets you see all of the fields and controls on the open form.

Sorry about that - I just don't write a lot of code directly on forms these days.

That worked, thanks!  But, I want to add a sum to the end  - is that possible?  It is not picking up the Sum([Svc_total]) in the amount

DSum("[item_total]","tbl_Quote_Equipment","[QuoteNo]= '" & [QuoteNo] & "' and ([Class] = 'Service' or [Class]= 'Commercial Service')")+Sum([Svc_Total])

I don't think Sum([field]) has any meaning in the context of a form. A form can be bound to a table, as I gather yours is (couldn't do the other stuff otherwise), but it is only "aware" of 1 record at a time - the "current" record, as indicated by the navigation control at the bottom of the form. Sum is a function applied to multiple rows of the table.

So, what's the difference between [item_total] and [svc_total]? Is this like "parts and labor"? If so, you need to use another DSum formula - same target table, same criteria, just a different field ([svc_total] instead of [item_total]).

DSum("[item_total]","tbl_Quote_Equipment","[QuoteNo]= '" & [QuoteNo] & "' and ([Class] = 'Service' or [Class]= 'Commercial Service')")+DSum("[svc_total]","tbl_Quote_Equipment","[QuoteNo]= '" & [QuoteNo] & "' and ([Class] = 'Service' or [Class]= 'Commercial Service')")

Select allOpen in new window

Hope that helps.

Yes, it is 2 fields like parts and labor.  The 1st part has 2 criteria - quote no and class, the 2nd part only has 1 criteria - quote number.  

This works as long as it finds the criteria in part 1, if the criteria is not found in the 1st part, the field is completely blank.


=DSum("[item_total]","tbl_Quote_Equipment","[QuoteNo]= '" & [QuoteNo] & "' and [Class] = 'Service'")+DSum("[svc_total]","tbl_Quote_Equipment","[QuoteNo]= '" & [QuoteNo] & "'")

Great that worked! Thanks so much!!!!!