I'm trying to count the number of positive values in a list, however the count function obviously just tells me how many rows there are in total. What I need to be able to do is count only the values that are not equal to Zero, however nothing I try seems to work. Any ideas?
what do you count?
In your question you say report and list, so do you have a report, a listbox or maybe a query?
If it is a query you have to make another query first with the condition (<>0) and the make a new query, based on the first one with the count in it.
Sorry, didn't make myself very clear, i'm creating a report based on a query. I can see what you mean about creating a 'count query' however it wouldn't be at all practical, as I need to do it for about 200 values. And I really can't warrent creating 200 seperate queries. I need to actually see the zeros on the report, but not count them.
believe me I have tried that time and time again, but the only way is to create two queries, one with the condition and one with the count.
You could however fill the field where the count value is with the count of a recordset.
(I use DAO for the recordsets because I don't know what version you use)
Dim db As DAO.Database
Dim rs As DAO.Recordset
set db = CurrentDb
set rs = db.OpenrecordSet ("Select * from Query1 Where Field1<>0")
If Not rs.EOF Then