Count Where

[aubrey]

Hi,

I've tried every combination I can think of to get all results from a query
where a selected field is more than zero; this is the expression that I used:

Amount In Count: Count([Amount In]>0)

Again, I trying to count all records in the Amount In filed that are more
than zero.

Any help with this would be greatly appreciated.

 

[Dirk Goldgar]

Hi,

I've tried every combination I can think of to get all results from a
query
where a selected field is more than zero; this is the expression that I
used:

Amount In Count: Count([Amount In]>0)

Again, I trying to count all records in the Amount In filed that are more
than zero.

Any help with this would be greatly appreciated.

Here's one way:

Amount_In_Count: Sum(IIf([Amount In] > 0, 1, 0))

 

[Daryl S]

Aubrey -

Leave the field as [Amount In]. In the Totals row, choose Count. In the
criteria row, put >0.

Now run the query.

 

[Dirk Goldgar]

Aubrey -

Leave the field as [Amount In]. In the Totals row, choose Count. In the
criteria row, put >0.

Now run the query.

That was my first thought, too, but that only works when the only records
you're interested in at all are those with [Amount In] > 0. That may not be
the case.

 

[aubrey]

Thank for the response Daryl. For some reason the count that I get as a
result include all of the records that were zero. when i run a regular query
with >0 as my criteria, the results eliminates the instances of zero. when i
enable the Totals row and choose Count the instances of zero are counted. i'm
not sure if this being a currency field matters; any additional help would be
greatly appreciated.

Aubrey -

Leave the field as [Amount In]. In the Totals row, choose Count. In the
criteria row, put >0.

Now run the query.

--
Daryl S

Hi,

I've tried every combination I can think of to get all results from a query
where a selected field is more than zero; this is the expression that I used:

Amount In Count: Count([Amount In]>0)

Again, I trying to count all records in the Amount In filed that are more
than zero.

Any help with this would be greatly appreciated.

 

[KARL DEWEY]

Try this --
Amount In Count: IIF([Amount In]>0, 1, 0)
Set the Group By to Sum.

--
Build a little, test a little.

Thank for the response Daryl. For some reason the count that I get as a
result include all of the records that were zero. when i run a regular query
with >0 as my criteria, the results eliminates the instances of zero. when i
enable the Totals row and choose Count the instances of zero are counted. i'm
not sure if this being a currency field matters; any additional help would be
greatly appreciated.

Aubrey -

Leave the field as [Amount In]. In the Totals row, choose Count. In the
criteria row, put >0.

Now run the query.

--
Daryl S

Hi,

I've tried every combination I can think of to get all results from a query
where a selected field is more than zero; this is the expression that I used:

Amount In Count: Count([Amount In]>0)

Again, I trying to count all records in the Amount In filed that are more
than zero.

Any help with this would be greatly appreciated.

 

[aubrey]

Works great, thank you very much

Try this --
Amount In Count: IIF([Amount In]>0, 1, 0)
Set the Group By to Sum.

--
Build a little, test a little.

Thank for the response Daryl. For some reason the count that I get as a
result include all of the records that were zero. when i run a regular query
with >0 as my criteria, the results eliminates the instances of zero. when i
enable the Totals row and choose Count the instances of zero are counted. i'm
not sure if this being a currency field matters; any additional help would be
greatly appreciated.

Aubrey -

Leave the field as [Amount In]. In the Totals row, choose Count. In the
criteria row, put >0.

Now run the query.

--
Daryl S


:

Hi,

I've tried every combination I can think of to get all results from a query
where a selected field is more than zero; this is the expression that I used:

Amount In Count: Count([Amount In]>0)

Again, I trying to count all records in the Amount In filed that are more
than zero.

Any help with this would be greatly appreciated.

 

[John Spencer]

Another way would be

Count(IIF([Amount In]>0,1,Null))

Count counts the presence of a value other than NULL. So by forcing a null to
be returned in Amount In is not > than 0 you get an accurate count.


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

Works great, thank you very much

Try this --
Amount In Count: IIF([Amount In]>0, 1, 0)
Set the Group By to Sum.

--
Build a little, test a little.

Thank for the response Daryl. For some reason the count that I get as a
result include all of the records that were zero. when i run a regular query
with >0 as my criteria, the results eliminates the instances of zero. when i
enable the Totals row and choose Count the instances of zero are counted. i'm
not sure if this being a currency field matters; any additional help would be
greatly appreciated.

:

Aubrey -

Leave the field as [Amount In]. In the Totals row, choose Count. In the
criteria row, put >0.

Now run the query.

--
Daryl S


:

Hi,

I've tried every combination I can think of to get all results from a query
where a selected field is more than zero; this is the expression that I used:

Amount In Count: Count([Amount In]>0)

Again, I trying to count all records in the Amount In filed that are more
than zero.

Any help with this would be greatly appreciated.