#error message in report from 0 results in query

Topic

New Reply

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

Reply | Quote

Replies

have you tried NZ
NZ[field name]

Reply | Quote

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”));

Reply | Quote

Try this. I also aliased your table/query[indent]

---

FROM [segregate no bookings] AS NoBook

---

[/indent]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”));

Reply | Quote