Expression Builder and Count

[DavidQMP]

Hi All,

I just joined the forum as I'm going to be using quite a lot of new queries over the next few months, but I've become a bit stuck so hopefully someone can point me in the right direction.

I want to count each row only if it is not null. So basically i want to end up with a calculated column that shows me what % of the target other columns are complete (not null).

Unique Order NumberColourPaper SizeCost% Complete1RedA4
75%
2BlueA32.75100%


A23.0050%4


25%


I've tried using the count function and still can't manage to get it right. This is the latest attempt to simply count the non-null values (in the expression builder):

Expr1: Sum(IIf(IsNull([table1]![UniqueOrderNumber]),0,1)+IIf(IsNull([table1]![Colour]),0,1)+IIf(IsNull([table1]![PaperSize]),0,1)+IIf(IsNull([table1]![Cost]),0,1))

Any help would be greatly appreciated.

David

 

[JHB]

..
I've tried using the count function and still can't manage to get it right. This is the latest attempt to simply count the non-null values (in the expression builder):
...

Do you get a wrong result/an error or what is your problem?

 

[MarkK]

So, in each row, you want to count the columns that are null, in that row?

If that's right, you could do something like. . .

SELECT *, Abs((Field1 Is Null)-(Field2 Is Null)-(Field3 Is Null)-(Field4 Is Null)) AS NullCount
FROM YourTable

True is -1, and (Field1 Is Null) returns True if the field is null, so we subtract and keep subtracting the negative ones (-1s) (Trues), and then take the Abs() of all of them. You could also just do . . .

SELECT *, -((Field1 Is Null)-(Field2 Is Null)-(Field3 Is Null)-(Field4 Is Null)) AS NullCount
FROM YourTable

. . . and negate the whole batch of them, but either way, that will be the count of the Null fields in the row.

 

[arnelgp]

correct me if im wrong, you want to include in count a record if:

Expr1: FormatPercent(((Not IsNull([table1].[UniqueOrderNumber])) +
(Not IsNull([[table1].[Colour])) +
(Not IsNull([table1].[PaperSize])) +
(Not IsNull([table1].[Cost])) * -1) / 4)