Count <>0 not working

B

Ben

Hi. I have field NWTH in table Balances. In the query view, I'm trying to
count the # of records where NWTH doesn't = 0. All of the records have the
value of 0 (not blank or null.) However, when I use the Totals function
(Count) and input the criteria <>0, it counts all the records resulting in
134 instead of 0. I expect the result to be 0 since all the records have a
value of 0 in the NWTH field.

Thanks in advance.
Ben

PS - I'm no good with SQL, so any advice for the Design view is appreciated.
 
K

Klatuu

Can you post the code you are using?
Hard to debug it if we can't see what you are doing.
 
M

Marshall Barton

Ben said:
Hi. I have field NWTH in table Balances. In the query view, I'm trying to
count the # of records where NWTH doesn't = 0. All of the records have the
value of 0 (not blank or null.) However, when I use the Totals function
(Count) and input the criteria <>0, it counts all the records resulting in
134 instead of 0. I expect the result to be 0 since all the records have a
value of 0 in the NWTH field.


Count(NWTH) counts all records with a non Null value in the
field NWTH so your result is expected.

Try using:
Count(IIf(NWTH <> 0, 1, Null))
or
Sum(IIf(NWTH <> 0, 1, Null))
or
Sum(IIf(NWTH <> 0, 1, 0))
or
Abs(Sum(NWTH <> 0))
or
-Sum(NWTH <> 0)
whichever is easiest for you to understand.
 
B

Ben

SELECT [Plan Names].[Plan ID], [Plan Names].[Rep Code], [Plan Names].[Plan
Sponsor], Avg([SDA Balances].NWRTH) AS AvgOfNWRTH, Count([SDA
Balances].NWRTH) AS CountOfNWRTH
FROM [SDA Balances] INNER JOIN [Plan Names] ON [SDA Balances].RGST_REP =
[Plan Names].[Rep Code]
GROUP BY [Plan Names].[Plan ID], [Plan Names].[Rep Code], [Plan Names].[Plan
Sponsor]
HAVING (((Avg([SDA Balances].NWRTH))<>0) AND ((Count([SDA
Balances].NWRTH))<>0));
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top