Show criteria in a report even if no records in recordset?

This returns a blank report with the input criterion at the top even if no records are returned.

1. Use an unbound report.

2. Declare a public variable like "dtStartDate" in the Code window for the report.

3. Add a textbox to the report for the public variable like "=dtStartDate"

In the report's Open event:
4. Use an inputbox to set the value of the public variable.
5. Set the report's recordsource to an SQL query with a "WHERE" clause that uses the public variable as a criterion.

Example:

Me.RecordSource = "SELECT YourTable.SomeField, YourTable.DateField FROM YourTable WHERE (((YourTable.Datefield)>#" & dtStartDate & "#));"


When a user opens the report, the inputbox gets the criterion, which shows up in the textbox regardless of the outcome of the query.

FAQ Mk II: a considerable improvement

A little smoother method makes use of a Public variable in a module in the database so that you don't have to write SQL.

- Put a Public Variable in the module like:

Public dtStartDate as Date

- Add a function to the module like:

Public Function GetTheDate()
GetTheDate = dtStartDate
End Function

- Use an inputbox in the Open Event of the report to set dtStartDate to the date of interest

- Add a criterion to your recordsource query "date" field of GetTheDate()

- Put a textbox on the form with the datasource set to: = GetTheDate()

That way the inputbox sets the public variable, which your little "GetTheDate()" function then retrieves as needed (to filter the records in the recordsource, display on the report, etc.)