IIf Statement Help on Between Dates

[jkdalex]

Hi I am trying to do an IIf statement for my query criteria. It tests if a form's two date fields are null. If so, then just list all cases for the field. If not (i.e. form field is filled) then only list those that fall between two dates from the form. I have tried everything but to no avail. This is what I had but it does not work. What can I do to get the "between dates" behavior?

Criteria:

IIf(IsNull([Forms]![frm_Search]![StartDate]) Or IsNull([Forms]![frm_Search]![EndDate]),[Date],[Date]>=[Forms]![frm_Search]![StartDate] and [Date]<=[Forms]![frm_Search]![EndDate])

Note that [Date] is the query field itself.

I have tried

Date]>=[Forms]![frm_Search]![StartDate] and [Date]<=[Forms]![frm_Search]![EndDate]

and

Date]>=DateValue([Forms]![frm_Search]![StartDate]) and [Date]<=DateValue([Forms]![frm_Search]![EndDate])

to no avail. What is the correct way to get a between dates in an IIf statement to be displayed?

 

[NumbersMax]

Welcome to the board!

I wasn't able to figure out how to get that IIF statement to work, but a different approach seemed to get the job done...try this:

in your query, add 2 expressions set as Group By. You can uncheck the "Show" box if you don't want to see the true/false results.

Exp1: IsNull([Forms]![frm_Search]![StartDate])
Exp2: IsNull([Forms]![frm_Search]![EndDate])

Set the criteria as follows:

Field---------Criteria Row#
Date field---1 Between [Forms]![frm_Search]![StartDate] and [Forms]![frm_Search]![EndDate]
Exp1 field---2 True
Exp2 field---3 True

Hope this helps!
Max

 

[jkdalex]

awesome, that works perfectly. thank you!