Count <>0 not working

[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.

 

[Klatuu]

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

 

[Marshall Barton]

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.

 

[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));