[AccessD] Missing months in a crosstab query

I have the following crosstab query which I am using to bind to a report:

TRANSFORM Sum(tlnklodgingEvent.Guests) AS [Number]

SELECT IIf([weekend]=True,"Cabin occupancy/Weekend","Cabin
occupancy/Midweek") AS Type, "Cabin" AS Category

FROM tblEvent INNER JOIN tlnklodgingEvent ON tblEvent.InvoiceNo =
tlnklodgingEvent.EventID

WHERE (((tlnklodgingEvent.LodgingID)=5 Or (tlnklodgingEvent.LodgingID)=7 Or
(tlnklodgingEvent.LodgingID)=27 Or (tlnklodgingEvent.LodgingID)=30))

GROUP BY IIf([weekend]=True,"Cabin occupancy/Weekend","Cabin
occupancy/Midweek"), "Cabin"

PIVOT Month(tblEvent.Beginningdate);

 

I want to have the report show all months even though there may be no data
for a particular month.  In the above query, I get data for January through
June and September through December.  Nobody stayed in cabins during July
and August.  So in the report I want to show zeroes in those columns.  I
tried using text boxes and IIF statements to test for null but I knew that
wouldn't work because month numbers 7 and 8 aren't null they just don't
exist in the query.  I tried looking in help to see if there was an "if
exists" function to see if I could see if the field exists in the query.  I
couldn't find anything like that.  Should I be working at the query level
instead to have the query insert a zero if there are months missing?  If so
I will need help on that also.  Thanks in advance for any help.

 

Susan Klos

Senior Database Analyst

Evaluation and Reporting

Florida Department of Education

850-245-0708 

sc 205-0708