COUNT or IF Function

P

PlsHelpme

I need to count a field only if it is NOT equal to 0. I would us
COUNTIF in Excel, however not sure how to do this in Access
 
P

PlsHelpme

NBVC;455546 said:
From Google researching, it looks like you will need something like:

COUNT(IIF(condition,1,0))

gotten from here:
Click to expand...

Thanks! I tried something like that and it doesn't work....
=Count(IIf([Installed]>"0"))
Where "Installed" is my field nam
 
J

John W. Vinson

I need to count a field only if it is NOT equal to 0. I would use
COUNTIF in Excel, however not sure how to do this in Access.
Click to expand...

What's the context? Are you using a Query? If so do you just want a count of
records where one field in the record is not equal to zero, or do you want to
retrieve all records and have a count (somewhere else) of the nonzero values?
 
H

Hans Up

PlsHelpme said:
I need to count a field only if it is NOT equal to 0. I would use
COUNTIF in Excel, however not sure how to do this in Access.
Click to expand...

In a query:

SELECT Count(*)
FROM YourTable
WHERE YourField <> 0;

Elsewhere, you might find the DCount function useful:

Debug.Print DCount("*", "YourTable", "YourField <> 0")

If you want more information, tell about where you hope to do your
counting. A form? A report? Somewhere else?
 
N

NBVC

Douglas said:
Wherever you found that, it's wrong.

You need Sum, not Count.

--
Doug Steele, Microsoft Access MVP
'Doug Steele's Beer and Programming Emporium' ()
(no e-mails, please!)



Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
Click to expand...

...sorry, my mistake...

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com
 
J

John Spencer

Count(IIF([Name of Field]=0,Null,[Name of Field]))

That counts all the fields with a value that is not null and is not zero.
Count counts the presence of a value, to the only thing it does not count is
when the value is null.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Top