Counting records that are not 0

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have a query that I want to count the number of records where the value of
a field "txtFactClients" is not 0. I have set up an expression "CountFC:
txtFactClients" with Count selected in the Total line and [txtFactClients]>0
in the criteria line but when I run the query it counts every record whether
or not it is 0. I've looked at previous post but they all seem to involve
SQL, is there a way of doing this in the design mode of a query?
TIA
Tony
 
Remove your expression and just pull down txtFactClients into the query
grid. Set the criteria as:
<>0
Count needs to be selected in the Total line.
Access will rename the field "CountOftxtFactClients" in the query results.
 
One method would be to use something like the following.

Field: IIF(txtFactClients = 0,Null,txtFactClients)
Total: Count

When Counting Access does not care what the value is. It counts whether or not
any value exists. The only thing it does not count is null values.
 
Thanks John
Tony
John Spencer (MVP) said:
One method would be to use something like the following.

Field: IIF(txtFactClients = 0,Null,txtFactClients)
Total: Count

When Counting Access does not care what the value is. It counts whether or not
any value exists. The only thing it does not count is null values.

Tony said:
I have a query that I want to count the number of records where the value of
a field "txtFactClients" is not 0. I have set up an expression "CountFC:
txtFactClients" with Count selected in the Total line and [txtFactClients]>0
in the criteria line but when I run the query it counts every record whether
or not it is 0. I've looked at previous post but they all seem to involve
SQL, is there a way of doing this in the design mode of a query?
TIA
Tony
Click to expand...
Click to expand...
 
Back
Top