INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access Howto:

Show criteria in a report even if no records in recordset? by paron
Posted: 24 Oct 01

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

Back to Microsoft: Access Other topics FAQ Index
Back to Microsoft: Access Other topics Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close