Now an easy one.... 2

[mikelev]

When running a report based on a query with a "Start Date" and "End Date" parameters, what do I need to do so that when enter is pressed at dialog box (no date entered) I will fetch all records?

Cheers,

 

[dhookom]

First off, I recommend using controls on forms rather than Parameter/Prompts. However, you can use
BETWEEN Nz([Enter Start],#01/01/1900#) AND Nz([Enter End],#12/31/3000#)

This assumes you don't date values lower or higher than the values supplied.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[mikelev]

Thank you tons! Have a star!

Can you explain how to use controls on reports vs. parameters?

 

[dhookom]

You can create a form with a couple text boxes for start date and end date. You can then set the criteria in your report's record source to something like:
Between Nz(Forms!frmNewForm!txtStartDate,#01/01/1900#) AND Nz(Forms!frmNewForm!txtEndDate,#12/31/3000#)

I much prefer using code in the On Click event of a command button like:
Dim strWhere as String
strWhere = "1 = 1 "
If not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND [datefield] >=#" & Me.txtStartDate & "# "
End If
If not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " AND [datefield] <=#" & Me.txtEndDate & "# "
End If
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[THWatson]

Duane
I was interested in this thread.

Can you explain why the method you outline is preferable to using the text boxes on a form to feed the StartDate and EndDate to the query?

Many thanks.

Tom

 

[THWatson]

Duane
Well, I can see one obvious advantage. The query isn't tied to this one report, so can be used for other things. Are there other advantages? Resources?

Tom

 

[dhookom]

Tom,
I just think it is more flexible in setting up criteria. You can check for Null values and add code for multi-select list boxes as explained in faq703-3936.

The major disadvantage is that you can't filter a subreport using this method (code).

My "growth" in criteria was:
1) different report for each data set with hard-coded criteria
2) parameter/prompts in the queries
3) references to controls on forms
4) building where clause in code

I use both 3 and 4 in my applications but never 1 and 2.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[THWatson]

Duane
Thanks for your explanation.

Tom