Am I doing it wrong? Null Values & Date()

[the problem]

The database runs perfectly when there is data available and when no work is found, my reports and queries generate zero results. I understand the logic; however, if no data is present I would like the query to display today's date and a zero. I'm very new to this and have no clue where to put "Nz()" or "IIF(,,)". I would really appreciate any insight.

Would this be easier if I feed the parameter from a form? I would prefer to avoid the form as I am setting up a report to run automatically via a reminder in Outlook.

SELECT [Assistance].[Call Date], Count([Assistance].[Call Date]) AS [CountOfCall Date]
FROM [Assistance]
WHERE ((([Assistance].[Call Date])=Date()))
GROUP BY [Assistance].[Call Date];

Thank you all in advance.

[Robeen]

I don't know if what you want to do can be done 'within' your query [Eg: when you run it].
I mean - your query says, "SELECT [Assistance].[Call Date], Count([Assistance].[Call Date]) AS [CountOfCall Date]" - right? So those fields are being selected.

If I needed to do what you're trying, I might try using some simple VBA Code that could be run from a Command Button on a Form [I know . . . I know . . . you don't want to get into that!!


Basically what you'd be doing would be creating three queries in a VBA Module - or behind a command button or something.
The first one would do a Count (*) of all the records that meet your criteria.
If the count is 0 then run a query like this:
"Select Date() as ReportDate, 0 As NumberOfRecords;"
If Count is > 0 then run the query you already have.

If this is something you'd like to try doing - let me know. I don't know how you would run a Query and NOT show fields based on whether or not there are records returned. I'm not saying it's not possible - just that if it is - I don't know how.

[the problem]

Yes please show me.

[Robeen]

I just read your first post through again and realized that what you are wanting is that your Report should say '07/25/2012 No Data Available' - or something like that when there is no data for the report - right?

There is an 'On No Data' Event for every Report - I was not aware of that till I googled it [because I write reports in other report-creation software [Cognos, Crystal Reports . . .] that have that feature - and while it looks like Access handles this differently - I haven't thoroughly researched it.

But - with what I have read - it might be easier than I thought.

There may be a more elegant way of doing this - but this is easy & will work.
1.
Create a Query like I showed you earlier:

Select Date() as ReportDate, 0 As NumberOfRecords;

and name it 'NoData' [for instance].

2.
a. Make a Copy of your existing Report and name it something like - YourReportName_NoData.
b. You will use this report [minus the data fields] as a 'template' for the 'NoData' version of the report.
c. Delete all the Fields from the Detail Section of the Report.
d. Go to Property Sheet & Make sure that 'Selection Type:' . . . is 'Report'.
e. Click on the Data Tab & change the Record Source to 'NoData' - [to connect the report to the NoData query you created in Step 1].
f. Click on the large 'Add Existing Fields' button towards the top of the screen - this will bring up the two fields from the NoData query.
g. Add those two fields to your report.
h. You'll have to decide how you want the Date and the NumberOfRecords fields to be displayed - and whether you want to keep the column headers from the original report or not . . .

3. Open the 'YourReportName' [your existing report] report in Design View.
4. Go to Property Sheet [make sure that the Property Sheet says Selection Type: Report] -> Event -> On No Data.
5. Click on the [...] to the right of the On No Data row and select Code Builder.
6. In the Code Window that opens - paste in:

DoCmd.Close acReport, "Courses"
DoCmd.OpenReport "Table3", acViewPreview

Your VBA screen should look like this:

Private Sub Report_NoData(Cancel As Integer)
DoCmd.Close acReport, "YourReportName"
DoCmd.OpenReport "YourReportName_NoData", acViewPreview
End Sub

Now - when you run 'YourReportName' - if there is no data, the VBA will close 'YourReportName' and open 'YourReportName_NoData'.

I hope this helps!