Calculated Conditional Expressions

[Caburky]

Is there a way to calculate conditional expressions? For example:
Total: IIf([field1]>0,1,0) + IIf([field2]>0,1,0) + IIf([field3]>0,1,0))) or
is there a better way to go about this?
Thanks!!

 

[Allen Browne]

The expression you have there is about as good as it gets.

If any field is null, the Else part will be the result, so your expression
suppresses zeros and negative values and handles nulls. Additionally, JET
interprets IIf() results correctlly, where it does not handle Nz()
correctly, and JET has its own IIf() so it's probably as efficient as it
gets.

In general, if you have lots of fields you are trying to add across a
record, you have built a spreadsheet instead of correctly designed database
table. In a relational database, these should be many *records* in a related
table instead of many fields across the one record.