Form to Filter Data

[theSizz]

I'm using a form to filter data for a report. The form has 5 unbound text boxes for the user to enter the criteria. The unbound text box names are Type, StDt, EndDt, Event, and Loc.Sometimes the user doesn't need to fill in all of the 5 unbound boxes that's on the form and leaves some of them blank. If that's the case I use the IsNull expression.I have the following expression in one of the the criteria panes of the query bound to this form.

IIf(IsNull([Forms]![frmSearchAll]![Type]),[tblRecap].[Type],[Forms]![frmSearchAll]![Type])

This works fine. My problem is with the syntax for criteria that contains the between operator. This is the syntax I'm using in the criteria pane of the query:

Between IIf(IsNull([Forms]![frmSearchAll]![StDt]),[tblRecap].[Date],[Forms]![frmSearchAll]![StDt] And [Forms]![frmSearchAll]![EndtDt])

I know this syntax is wrong because the query produces the wrong result. Can someone please help with the proper syntax?

 

[Fizzio]

Between IIf(IsNull([Forms]![frmSearchAll]![StDt]),[tblRecap].[Date],[Forms]![frmSearchAll]![StDt]) And [Forms]![frmSearchAll]![EndtDt]

 

[theSizz]

form to filter data

Thanks Fizzio for the help. I realize I made a typo when I posted the expression. The parenthesis at the end of the expression should be ommited. I have it without the trailing parenthesis in my query and it doesn't work. The proper expression follows:

Between IIf(IsNull([Forms]![frmSearchAll]![StDt]),[tblRecap].[Date],[Forms]![frmSearchAll]![StDt]) And IIf(IsNull([Forms]![frmSearchAll]![EndDt]),[tblRecap].[Date],[Forms]![frmSearchAll]![EndDt])

I can't take credit for this, this solution was given to me by a friend.

Thanks for your response. Much appreciated.

The Sizz

 

[Fizzio]

No problem. Did not realise you needed the second IIf statement.