DSum with multiple and optional criteria (1 Viewer)

[Nevsky78]

Dear all,

I have a table of data (tbl_marginsummary) with a record for every combination of Customer, Season, Brand, Month and StockCode.

What I am trying to achieve with my frm_summary is some kind of DSum (or a query of possible) based on any, all or some of the comboboxes I have at the top of the form.

For example, if the Brand was selected as "Brand 1" but the rest of the comboboxes were blank, I would like to see the totals below for just that Brand.

If both Brand and Customer had a selection, I would therefore want the totals below for both the Brand and Customer combination.

Does anyone know the best way to go about this?

I was thinking of a DSum with multiple criteria but I don't know how to account for Null values in each separate criteria.

Thanks!
Nick

 

[stumac]

I would recommend that you have your form bound to a totals query.

Sum the fields you want totals for in the query and the fields that have possible criteria use the where clause, within the criteria for each of these fields reference back to the form like this : ' [Forms]![frm_summary]![cboCustomer] or [Forms]![frm_summary]![cboCustomer] is null '


Stu