Results 1 to 4 of 4
  1. #1
    Stumper
    Guest

    #error message in report from 0 results in query

    I am tryng to design a report that queries my table for information on the geographic source of reservations, then summarizes each geo source category. Because we analyze different areas more closely (Canada is separated into provinces, some provinces are separated into cities, etc) the report has many subreports so that it all lays out clearly.

    My problem occurs when the query returns no results in one category. This causes an #error message to appear in that control, and then all controls further down the line inherit that error message. How can I make the query return 0 so that the report can continue instead of giving me the error???

    TIA

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #error message in report from 0 results in query

    have you tried NZ
    NZ[field name]

  3. #3
    Stumper
    Guest

    Re: #error message in report from 0 results in query

    Hi Jerry -

    I did some reading in the help file and it appears that NZ will do what I'm looking for. However, I am unclear on how to use it!

    I am pretty new to programming in Access (but getting better). I can read the SQL code somewhat, but have no idea how to write it. I have pasted the text of the query below - could you indicate where the function is used??

    Thanks Much.

    SELECT DISTINCTROW [segregate no bookings].[GEOGRAPHICAL SOURCE OF BOOKING], Sum([segregate no bookings].NIGHTS) AS SumOfNIGHTS, Sum([segregate no bookings].REVENUE) AS SumOfREVENUE, Count(*) AS [Count Of segregate no bookings]
    FROM [segregate no bookings]
    GROUP BY [segregate no bookings].[GEOGRAPHICAL SOURCE OF BOOKING]
    HAVING ((([segregate no bookings].[GEOGRAPHICAL SOURCE OF BOOKING]) Like "MARITIMES"));

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

    Re: #error message in report from 0 results in query

    Try this. I also aliased your table/query
    <hr>FROM [segregate no bookings] AS NoBook<hr>
    so that you didn't have so much typing of that long, full of spaces, table or query name. It should also make the query run faster (don't ask me how, but it does seem to).

    SELECT DISTINCTROW NoBook.[GEOGRAPHICAL SOURCE OF BOOKING],
    Sum(Nz(NoBook.NIGHTS,0)) AS SumOfNIGHTS,
    Sum(Nz(NoBook.REVENUE,0)) AS SumOfREVENUE,
    Count(*) AS [Count Of segregate no bookings]
    FROM [segregate no bookings] AS NoBook
    GROUP BY NoBook.[GEOGRAPHICAL SOURCE OF BOOKING]
    HAVING (((NoBook.[GEOGRAPHICAL SOURCE OF BOOKING]) Like "MARITIMES"));
    Charlotte

Posting Permissions

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