Count <>0 not working

  • Thread starter Thread starter Ben
  • Start date Start date
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.
 
Can you post the code you are using?
Hard to debug it if we can't see what you are doing.
 
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.
Click to expand...


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.
 
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));
 
Back
Top