Showing all records when criteria not chosen

  • Thread starter Thread starter Deb Smith
  • Start date Start date
D

Deb Smith

I have a form with a series of unbound text boxes and combo boxes that are
used to set the query parameters for a report. Once the user chooses the
criteria from the various boxes, the desired report opens in preview mode.

The text boxes used are for capturing beginning and end dates and use a
pop-up for a calender control. In the underlying query for the report I am
using the following statement in the criteria for the date field called
ToDoDate.

Between [Forms]![To Do List Dialog]![BeginningDate1] And [Forms]![To Do List
Dialog]![EndingDate1].

I would like to have it so that If the user does not select a beginning and
end date but chooses the other criteria criteria required that all records
are shown on the report. I am not sure how this is done.In the past I have
used a statement like the following, but I can't get it to work with this
date field.

Like IIf([Forms]![To Do List Dialog]![Occassion] Is Null,"*",[Forms]![To Do
List Dialog]![Occassion] & "*")

I really could use some help. Thanks
 
Hi,

It will probably be easiest to apply the date criteria as the Where
Condition of the report, then you can just leave the where condition blank
when the user doesn't set any dates.

In the click event for the button that is used to launch the report, you
would have something like:

If IsNull(Me.StartDate.Value) Or IsNull(Me.EndDate.Value) Then

DoCmd.OpenReport stDocName, acPreview
Else

DoCmd.OpenReport stDocName, acPreview, , _
"ToDoDate Between #" & _
Format(Me.StartDate, "mm/dd/yyyy") & _
"# AND #" & _
Format(Me.EndDate, "mm/dd/yyyy") & "#"

End If

You need to use "#" for literal dates and format it to the American date
system, if not already.

HTH

MFK.
 
Back
Top