We need to be able to restrict reports by range of start & stop dates. 2

[Sherman6789]

When our users run reports they need to be able to add a start and stop date at the beginning. The system will then filter out all other items before the report is run.

Please explain how this can be done. What code needs to be used and where? The method of using the procedures should be simple enough for persons to use who do not have technical skills in Access.

Thanks!

 

[dhookom]

There are lots of resources on the web that suggest how to filter a query, form, or report. These are the basics that I use.
1) use the command button wizard to create a button to open the report
2) make sure you have controls on your form for users to enter the criteria. Typically these will have names like: txtStart and txtEnd
3) open the code for the command button click event and modify it to look something like:

   dim stDocument as String
   Dim strWhere as String
   stDocument = "rptYourReportName"
   strWhere = "1=1 "
   If Not IsNull(Me.txtStart) Then
      strWhere = strWhere & " And [YourDateField] >=#" & _
            Me.txtStart & "# "
   End If
   If Not IsNull(Me.txtEnd) Then
      strWhere = strWhere & " And [YourDateField] <=#" & _
            Me.txtEnd & "# "
   End If
   DoCmd.OpenReport stDocument, acPreview, , strWhere

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[daver56]

If you base the report on a query, then you can design in a prompt to request the user provide the start and stop dates of the report. For example if the field containing the date was "xdate" then in the criteria area of the "xdate" column you would enter something like this

"=[Enter Date for Report]" (without quotes)

This would open a dialog box and display the text in the brackes, and use your response as the criteria.

You can also use more than one, for example if the field were in text format it would look like

"Between [Enter Start Date] and [Enter Stop Date]"

(again with out the quotes)

I am not sure if this will work in your case, but just thought I would toss it out.

 

[dhookom]

Parameter prompts work but they lack functionality. If you prompt for more than one, you only see one at a time. You also have the provide prompts every time you open the report.

If you use controls on a form, you can provide defaults, ignore nulls, use combo and list boxes, check for integrity and reasonableness,...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[daver56]

All very true. As a beginner my self I am forced to go the simple approach first.

Dave