criteria for date field using iif()

[QB]

I need to build a criteria from a date field pulling the criteria from a form
and am having some difficulty getting the synthax right

I have a form named rpt_Sel with a controls named status, From, To

In my query I am trying to set the criteria of a date field as follows

=iif(Forms![rpt_Sel].[status]="Open",Between Forms![rpt_Sel].[From] And
Forms![rpt_Sel].[To], Like "*" Or Is Null)

However, it is not returning anything in either case?? What is the proper
synthax?

QB

 

[Duane Hookom]

You can't place "Between" or "Like" operators inside the IIf().
Apparently this query is the record source of a report. If so, I would build
a where condition in code that dynamically create a filter.

You could also try something like:
Between Forms![rpt_Sel].[From] And Forms![rpt_Sel].[To] Or
Forms![rpt_Sel].[status]<>"Open"