Counting records that are not 0

[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

 

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

 

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

 

[Tony Williams]

Thanks John
Tony

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.

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