count function

[Guest]

What is the formula for counting different fields that are filled in with
data vs. blank or no entry in a form or query.
Example:
field 1 = 5, field 2 = 3, field 3 = blank, field 4 = 6, field 5 = blank
total count = 3

 

[Steve Schapel]

Robert,

Such computations *across* a row is more of a spreadsheet sort of
operation. It is unusual to want to do this in a properly designed
database table. There is no built-in formula/function for this. It
would be possible to write your own user-defined function.
Alternatively, you can use a calculated field in a query, something like
this (using your example)...
DataFields: Abs([field 1] Is Not Null And [field 2] Is Not Null And
[field 3] Is Not Null And [field 4] Is Not Null And [field 5] Is Not Null)

 

[Guest]

What Steve said.

Below is my take on the solution. One 'gotcha' is that a Null, Empty String,
and a field filled with spaces or non-printing characters all look blank but
are really different things. Both Steve's and my solutions only work with
nulls.

CountOf Fields: (IsNull([field 1])+1)+(IsNull([field 2])+1)+(IsNull([field
3])+1)+(IsNull([field 4])+1)+(IsNull([field 5])+1)