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.
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.)