Form selection with query

[teel73]

I have a query that runs based on selection from a form. My form has 2 fields [txtStart] and [txtEnd]. I want my query to return all records if those 2 fields are left null. But if the user enters a date into both I want the query to return records where the field [CompletedDate] is between [txtStart] and [txtEnd]. I can't seem to get the query right. Here's what I have in my query grid: I select all fields and the following expression field

Expr1: [CompletedDate] Between [Forms]![frm_FilterForm]![txtStart] And [Forms]![frm_FilterForm]![txtEnd] Or [Forms]![frm_FilterForm]![txtStart] Is Null And [Forms]![frm_FilterForm]![txtEnd] Is Null

 

[jzwp22]

An expression (or calculated) field won't do it; you need to change the criteria of the query depending on whether or not the user enters the dates. Instead of just returning the query results, it would be better to create a form that is based on your query but without the criteria. Then from your date input form change the way the newly created form opens depending on whether dates were entered or not (filtered or not filted on open). This will take some Visual Basic for Application (VBA) code.

I've attached a sample database that illustrates this technique. If either or both date controls on the form are left blank, all records are returned.