Use date range and include 'empty' dates in report

[KelmanBob]

I've devised a report for a touring theatre company for their shows list. I use a date range query to target different parts of their touring. I'd like for them to be able to see blanks in the report where there is no data for given dates(basically what gaps are left), for booking purposes. How would I go about that?

Many thanks.

 

[vbaInet]

I've devised a report for a touring theatre company for their shows list. I use a date range query to target different parts of their touring. I'd like for them to be able to see blanks in the report where there is no data for given dates(basically what gaps are left), for booking purposes. How would I go about that?

Many thanks.

the WHERE statement in your query for showing a specific date would look like this "WHERE showsDate Between #12/12/2009# And #1/1/2010", so to get a blank record, the Where statement should read "WHERE showsDate Is Null"

 

[KelmanBob]

Hi, thanks for this reply. Unfortunately I don't think I understand or I haven't explained what I'm after very well.

Firstly, I'm looking at records in a date range rather than a single date and so would need to combine the range of records that exist with instructions to show them within the context of all dates in the range, even if there is no recorded data.

I don't even know if this is possible. Is it possible to just generate a calendar without any records?

Or else what is the most efficient way of showing this data in another programme? Outlook seemed really unpromising but if there is a way...

Also the form of the showsDate implies that it is a function of Access but the only reference I find to it in Google is this thread...

Perhaps I'm just asking for something that can't be done... Or is it a third party app thing?

 

[Rich]

Change the Report's underlying Query's joins

 

[AlienRay]

What I usually do in cases like this is create a table that holds just a single date field. This table contains all possible dates within the range you're generating calendars for. (In my case, this usually means a full calendar year, but in your case, it may be a touring season.)

This table can be the basis for any forms or reports that need to show all dates. The table that holds the show dates will link to this table by the date field.

I've attached a sample of what I mean.

 

[KelmanBob]

Thank you AlienRay, that's very helpful. I was thinking I might have to put false or empty records in to generate the schedule in Access and kind of blind-sided myself. The separate table for all dates is a good workable solution.