Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Return 0 when there are no results in query (A97 SR2)

    When running a query and there are no results, the query responds by returning no lines.
    I want to have the query return a line with a zero when there are no results.

    SELECT tblDiag.Status, Count(tblDiag.Status) AS CountOfStatus
    FROM tblDiag
    WHERE (((tblDiag.DateOfCloseCall) Between [Start Date] And [End Date]))
    GROUP BY tblDiag.Status
    HAVING (((tblDiag.Status)="Open"));

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Return 0 when there are no results in query (A97 SR2)

    That isn't the nature of queries. Queries return records based on the SQL you passed them. They can't return non-existent records.
    Charlotte

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Return 0 when there are no results in query (A97 SR2)

    As Charlotte pointed out, a query can't do that. In your example, what should Status be in a "record" with CountOfStatus 0?

    I suppose that you want to use this in a form or report.

    There, you might work around it using DCount. This will return 0 if no records meet the condition argument.

    And in (sub)reports, you can check for HasData = 0.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Return 0 when there are no results in query (A97 SR2)

    Thank you, the both of you.

    I will strive to do this on the report instead of the query.

Posting Permissions

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