Combination of Field not empty

[YDN]

Hi with you all,
I can't obtain what I want with my queries.
I've got a table with a lot of fields witch I only want to test Not null case.

Sample of records

A, B, C, D,
10 20 5
8 9
100 10 10
10 4
10 20 5

In this sample of records, I'm interesting only in two case : A,B,D not null and A, B not null, I'm not interesting in the details.

I can only make a query like this :
SELECT DISTINCT TABLE.A, TABLE.B, TABLE.C, TABLE.D, Count(TABLE.NUM) AS NUMBEROFNUM
FROM

GROUP BY TABLE.A, TABLE.B, TABLE.C, TABLE.D
HAVING (((TABLE.A) IS NOT NULL)) OR (((TABLE.B) IS NOT NULL)) OR (((TABLE.C) IS NOT NULL)) OR (((TABLE.D) IS NOT NULL))
ORDER BY TABLE.A, TABLE.B, TABLE.C, TABLE.D

The query find :
A, B, C, D, numberofnum
10 20 5 2
100 10 10 1
10 4 1
8 9 1

But I can't reach to have what I search, something like (NE Not Empty) :
A, B, C, D, numberofnum
NE NE NE 3
NE NE 2

Is it possible ?

Thanks by advance for any help.
Thanks to have read until there.

 

[John Vinson]

But I can't reach to have what I search, something like (NE Not Empty) :
A, B, C, D, numberofnum
NE NE NE 3
NE NE 2

In that case, don't include the actual values of A, B, C, and D in
your query - you don't care about them anyway.

Try

SELECT IIf(IsNull([A]), "", "NE")) AS AExists,
SELECT IIf(IsNull(), "", "NE")) AS BExists,
SELECT IIf(IsNull([C]), "", "NE")) AS CExists,
SELECT IIf(IsNull([D]), "", "NE")) AS DExists,
Count(*) AS NUMBEROFNUM
FROM

GROUP BY AExists, BExists, CExists, DExists
WHER (((TABLE.A) IS NOT NULL)) OR (((TABLE.B) IS NOT NULL)) OR
(((TABLE.C) IS NOT NULL)) OR (((TABLE.D) IS NOT NULL));

John W. Vinson[MVP]

 

[YDN]

Hi with you all, Hi and thank you very much John W. Vinson,

I have already use the iif command in a query to ignore the null value but if the value wasn't null I displayed them !

Yesterday, it was a little bit different and I forgot this idea.

Thank you very much again.
Thanks to have read until there.

But I can't reach to have what I search, something like (NE Not Empty) :
A, B, C, D, numberofnum
NE NE NE 3
NE NE 2

In that case, don't include the actual values of A, B, C, and D in
your query - you don't care about them anyway.

Try

SELECT IIf(IsNull([A]), "", "NE")) AS AExists,
SELECT IIf(IsNull(), "", "NE")) AS BExists,
SELECT IIf(IsNull([C]), "", "NE")) AS CExists,
SELECT IIf(IsNull([D]), "", "NE")) AS DExists,
Count(*) AS NUMBEROFNUM
FROM

GROUP BY AExists, BExists, CExists, DExists
WHER (((TABLE.A) IS NOT NULL)) OR (((TABLE.B) IS NOT NULL)) OR
(((TABLE.C) IS NOT NULL)) OR (((TABLE.D) IS NOT NULL));

John W. Vinson[MVP]