Null values in query

[weezles]

Hi

I have the following query:

SELECT Ethnicity, Count(Ethnicity) as Total
FROM qryEth
GROUP BY Ethnicity;

How can i deal with null values? The combo box contains 15 items and i only want to show those that are not null. The output from this query =

0
White 10
Mixed 2

I want to get rid of the blank field and the 0 for producing a tabular report. I have looked at the NZ function and IIF but the results could show different ethnic origins each time.

Can anyone help?

Thanks

Lou

 

[M8KWR]

change your sql to

SELECT qryEth.Ethnicity, Count(qryEth.Ethnicity) AS Total
FROM qryEth
GROUP BY qryEth.Ethnicity
HAVING (((qryEth.Ethnicity) Is Not Null));

that should do the trick, if not let me know

 

[weezles]

That worked a treat.

Thanks

 

[PHV]

And as the test doesn't need aggregate function one more efficient way:
SELECT Ethnicity, Count(Ethnicity) as Total
FROM qryEth
WHERE Ethnicity Is Not Null
GROUP BY Ethnicity

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244