Count Fields that are not null on a record

[Guest]

I have a form that has 40 fields that could or could not be populated. I
need to create a count for reporting purposes, counting those fields that are
populated with something.
I have attempted to use:

Sum(IIF([AccountNumber1] Is Not Null, 1, 0) + IIF([AccountNumber2] Is Not
Null, 1, 0) and so on for the 40 fields

but I have not gotten it to work - Is there an easier way to do this and if
not can someone please help me....

 

[Douglas J. Steele]

With IIf, you use the IsNull function: IS NULL is SQL:

Sum(IIF(IsNull([AccountNumber1]), 0, 1) + IIF(IsNull([AccountNumber2]), 0,
1)....)

And pardon the unasked-for advice, but if you have 40 fields named
AccountNumber1, AccountNumber2 and so on, your database isn't properly
normalized. Take a look at what Jeff Conrad has at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101
for some good references on database normalization.

 

[Pieter Wijnen]

Even simpler:
Sum(-IsNull([AccountNumber1])-IsNull([AccountNumber2])....)
or probably just
-IsNull([AccountNumber1])-IsNull([AccountNumber2])....

and he might just need it for PIVOT data <g>

Pieter

With IIf, you use the IsNull function: IS NULL is SQL:

Sum(IIF(IsNull([AccountNumber1]), 0, 1) + IIF(IsNull([AccountNumber2]), 0,
1)....)

And pardon the unasked-for advice, but if you have 40 fields named
AccountNumber1, AccountNumber2 and so on, your database isn't properly
normalized. Take a look at what Jeff Conrad has at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101
for some good references on database normalization.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a form that has 40 fields that could or could not be populated. I
need to create a count for reporting purposes, counting those fields that
are
populated with something.
I have attempted to use:

Sum(IIF([AccountNumber1] Is Not Null, 1, 0) + IIF([AccountNumber2] Is Not
Null, 1, 0) and so on for the 40 fields

but I have not gotten it to work - Is there an easier way to do this and
if
not can someone please help me....