Discussions

 View Only
Expand all | Collapse all

Count number of populated fields among a group of 10 numeric fields.

  • 1.  Count number of populated fields among a group of 10 numeric fields.

    Posted 07-10-2018 15:18
    I have a group of 10 numeric fields, some of them contain data and some don't. I want to count the number of fields which contain data, for example if field 1 contains <3241>, field 2 contains <4567>, field 3 is <empty> resulting total should be 2. I want to make this work in a calculated column so it can be used in reports. 


  • 2.  RE: Count number of populated fields among a group of 10 numeric fields.

    Posted 07-10-2018 15:22
    Count([field1], [field2], [field3])


  • 3.  RE: Count number of populated fields among a group of 10 numeric fields.

    Posted 07-10-2018 15:23
    You can use the count function to count "trues".  There is a question as to how to deal with blanks.  if the fields are NOT set to treat blank as zero (ie to treat them as null)

    Count(
    not IsNull([Field 1]),
    not IsNull([Field 2]),
    ...
    not IsNull([Field 10]))

    If they are set to treat blank as zero then

    Count(
    [Field 1]<>0,
    [Field 2]<>0,
    ...
    [Field 10]<>0)


  • 4.  RE: Count number of populated fields among a group of 10 numeric fields.

    Posted 07-10-2018 15:42
    Thanks this worked just fine!