Counting records that are not 0

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
 
P

PC Datasheet

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

John Spencer (MVP)

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

Tony Williams

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