Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Posts
    4

    Unhappy Unanswered: Counting Rows in Access Report

    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?

  2. #2
    Join Date
    Dec 2002
    Posts
    18
    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.

  3. #3
    Join Date
    Dec 2002
    Posts
    4
    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.
    Last edited by pj7aj; 12-10-02 at 06:16.

  4. #4
    Join Date
    Dec 2002
    Posts
    18
    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.
    Like this
    (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
    me.text1.Value=rs.RecordCount
    End If

  5. #5
    Join Date
    Dec 2002
    Posts
    4
    I'll give that a try. Cheers for the help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •