Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

I have discovered the answer to this question and it is in the use of IIF statement. As noted above, I have a table called TestFormula withe TestID, Value1, Value2 and Value3 as the fields. Creating the table in Query I write:

SELECT TestID, Value1, Value2, Value3, IIF(Value1 > 0, 1, 0) + IIF(Value2 > 0, 1, 0) + IIF(Value3 > 0, 1, 0) AS SUM FROM TestFormula;

The new field with the alias of SUM counts across the fields of Value1, Value2 and Value3 and yields the number of non-zero fields from 0 to 3.

Thanks to those who provided suggestions, they ultimately led me to what feels like a pretty simple answer. Larry

I have discovered the answer to this question and it is in the use of IIF statement. As noted above, I have a table called TestFormula withe TestID, Value1, Value2 and Value3 as the fields. Creating the table in Query I write:

SELECT TestID, Value1, Value2, Value3, IIF(Value1 > 0, 1, 0) + IIF(Value2 > 0, 1, 0) + IIF(Value3 > 0, 1, 0) AS SUM FROM TestFormula;

The new field with the alias of SUM counts across the fields of Value1, Value2 and Value3 and yields the number of non-zero fields from 0 to 3.3.

This solution could also work if I tried to count non-null fields if I wrote IIF(Value1 IS NULL, 0, 1) etc. but I don't have to go through the division I was originally doing which yielded the null values in the first place.

Thanks to those who provided suggestions, they ultimately led me to what feels like a pretty simple answer. Larry