Count empty fields

Question

Is it possible to count how many empty/null fields there are for a specific record? I have a table with a million (feels like) fields; lots of yes/no and dates to track completion of various tasks in a process, plus information relating to those tasks. I've created queries that show only the relevant fields for each stage of the process. So 'tqry01Application' shows only the fields relevant to Application; 'tqry02Compliance' shows only those relating to Compliance, etc. I'd like to be able to run a report that shows me how many empty fields there are for each stage for each record - e.g. Record #1 has 10 empty in tqry01Application, 15 empty in tqry02Compliance etc. I'm guessing I could have an unbound for each field "iif([Field1]is null, 0,1" and finally a sum of all those fields, but I'm wondering if there's a quicker way to do it.

Answer 1

You can use IsNull in an expression:

EmptyFields: Abs(IsNull([Field1]) + IsNull([Field2]) + .. + IsNull([FieldN]))

or pure SQL:

EmptyFields: Abs(([Field1] Is Null) + ([Field21] Is Null) + .. + ([FieldN] Is Null))