Count Where

  • Thread starter Thread starter aubrey
  • Start date Start date
A

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.
 
aubrey said:
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.
Click to expand...


Here's one way:

Amount_In_Count: Sum(IIf([Amount In] > 0, 1, 0))
 
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 said:
Aubrey -

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

Now run the query.
Click to expand...


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

Daryl S said:
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


aubrey said:
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.
Click to expand...
Click to expand...
 
Try this --
Amount In Count: IIF([Amount In]>0, 1, 0)
Set the Group By to Sum.

--
Build a little, test a little.


aubrey said:
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.

Daryl S said:
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


aubrey said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Works great, thank you very much

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

--
Build a little, test a little.


aubrey said:
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.

Daryl S said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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

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

--
Build a little, test a little.


aubrey said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top