"Generating" null dates for spreadsheet

[tyemm]

I hope that conveys something of what I want.
I want the output of a date range query to give me all dates (days) in a range, as opposed to only those dates with data. The reason is that I want to output to a spreadsheet (for charting) such that, for, say April, I will have 30 columns, even if only some smaller number of April dates have data.

Right now I'm using a "report generator form" to get my date range:
SELECT [ED Hold Daily Data].AdmitDate, [ED Hold Daily Data].TeamService, [ED Hold Daily Data].HoldTime
FROM [ED Hold Daily Data]
WHERE ((([ED Hold Daily Data].AdmitDate) Between [Forms]![frm ED Hold Report Generator]![cboStartDate] And [Forms]![frm ED Hold Report Generator]![cboEndDate]));

Then I have a cross-tab query working from this:

PARAMETERS Forms![frm ED Hold Report Generator]![cboStartDate] DateTime, Forms![frm ED Hold Report Generator]![cboEndDate] DateTime;
TRANSFORM Count([qry Monthly Hold Time].HoldTime) AS CountOfHoldTime
SELECT [qry Monthly Hold Time].TeamService, Count([qry Monthly Hold Time].HoldTime) AS [Total Of HoldTime]
FROM [qry Monthly Hold Time]
GROUP BY [qry Monthly Hold Time].TeamService
PIVOT Format([AdmitDate],"Short Date");

Thus I want to have (to output to Excel) a spreadsheet showing, for each date in the chosen month, the Total of HOldTime for each TeamSErvice. Perhaps there is some iterative process to get these zero dates?

Tom

 

[jsteph]

Try putting:

 In (5/1/2005,5/2/2005,5/3/2005,5/4/2005,5/5/2005,5/6/2005,5/7/2005...etc)

after the pivot (remove the semicolon). This is for may, of course, but this list will need to be generated with some function accepting the start/end dates that the query takes as parms, since obviously you won't want to type this all the time.

The format should match the format used in the Pivot.
--Jim

 

[tyemm]

Thank you kindly--
Now I will try and generate a sequence of dates, including every one from my cboStartDate through my cboEndDate. If anyone has a quick way to do that I would be most grateful. Just as important, I have been unable to Export a report based on this query into Excel for some reason. I have a nice report that appears as a spreadsheet, with all the totals I want (e.g. Date, Service, No. in Service, Total Hold time, etc.) but in Excel I get only an empty spreadsheet or an error opening a file.