Getting zeros like tinker

[markmywords]

I've read tinkers posting which I'm sure is the same problem but couldn't really get my head around it all.

Here's mine.

I have two combo boxes which select product types. The first is always selected and the second is sometimes selected. After a positive selection a Cost ID is returned into the query and the accompanying Unit Cost is used in an expression for a calculated field.
In the case of the second combo, when a product is not selected (a check box disables it) then I would like the calculated field to result in £0.00. Because there is no Cost ID and thus no Unit Cost associated, the expression just returns as null.

Do I need a Cost ID which relates to £0.00 Unit Cost which is then stamped into the query when no selection is made?
If so, how do I set this as the default in case of no selected product.

 

[tinker]

Your right, it does sound pretty similar to my problem. In my experience, using a switch statement is one way of solving it.

Im not entirely sure if you should put the code in your querie or in the SQL code behind your combo box (id imagine the latter, if this is the case you will have to enter this code into both combo boxes),

SWITCH ( Sum([field name here)<>null,Sum([field name here]),not( Sum([field name here])<>null), 0) AS [name here]
(the first part upto the not is saying if the value is not null then display the field value, otherwise display a 0.


(If you dont need the sum value take it out)