Criteria to count the data in fields in table or Query

[Srin]

I have a table with fields for Products of each type. Ex Product1, Product2, Product3 as fields. And another Field called Product Count in my table. I want the Count field to have a value of 1 as default and either 2 or 3 based on the result of all or some of the Product1,2,3 fields having values in the records.
Ex:

Product1	Product2	Product3	Count
xxxx			1
xxxx	xxxx		2
xxxx	xxxx	xxxx	3

I want to state that there will be by default atleast one order. Hence the possibility of 0 orders is not there. Which is the right way to achieve this result?

[June7]

The 'right' way is to normalize data structure and then do either: aggregate (GROUP BY) Totals queries or build report using its Sorting & Grouping feature with aggregate calcs in footer sections.

Otherwise, use IIf().

IIf(Product1 Is Null, 0, 1) + IIf(Product2 Is Null, 0, 1) + IIf(Product3 Is Null, 0, 1)

[Srin]

Where can I include this criteria to fill up the Count field? Will that be in a query as an Expression? Or can I do it in a table?

[June7]

Try a calculated field in table. See if it recognizes the IIf() function. Otherwise, do in query or textbox.

[Srin]

It worked perfectly in a query. Thanks @June7!!