Showing All Records when Criteria not chosen

[barit]

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]![ToDoListDialog]![BeginningDate1] And [Forms]![ToDoListDialog]![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]![ToDoListDialog]![txtboxname] Is Null,"*",[Forms]![ToDoListDialog]![txtboxname] & "*")

I really could use some help. Thanks

 

[dhookom]

I rarely place criteria like this in the query. In this case, I would remove the criteria and write some code like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.BeginningDate1) Then
    strWhere = strWhere & " And [SomeDate] >=#" & _
      Me.BeginningDate1 & "# "
End If
If Not IsNull(Me.EndingDate1) Then
    strWhere = strWhere & " And [SomeDate] <=#" & _
      Me.EndingDate1 & "# "
End If
DoCmd.OpenReport "rptYourReport",acPreview , ,strWhere

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

 

[barit]

Thanks so much for the response. It sure does the trick.

I would however, like to understand the code you have furnished and how it works.

What does strWhere = "1=1 " mean?
I am not sure how the code you provided in fact is setting the parameter to either the value chosen or if null --all records?

Once again, thanks for providing a solution. If possible, I would really like an explanation.

 

[PHV]

Have you tried to replace this:
Between [Forms]![ToDoListDialog]![BeginningDate1] And [Forms]![ToDoListDialog]![EndingDate1].
By this ?
Between Nz([Forms]![ToDoListDialog]![BeginningDate1], ToDoDate) And Nz([Forms]![ToDoListDialog]![EndingDate1], ToDoDate)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

I use
strWhere = "1=1 "
so that when I add to the where clause (strWhere), I always add the " AND ". If I didn't use 1=1 then I would have to check for the length of strWhere to see if the new addition to strWhere was the first or if it needed the " AND ".

The where clause in DoCmd.OpenReport (or OpenForm) sets the Filter property of the report.

The only instances where I use "hard-coded" references to controls on forms is for subreports and filtering on fields that aren't included in the report's record source.

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