IIf Statement Help on Between Dates

jkdalex

New Member
Joined
Jul 24, 2007
Messages
11
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:
Code:
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

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

and

Code:
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?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

NumbersMax

Board Regular
Joined
Jun 19, 2007
Messages
200
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
 

Forum statistics

Threads
1,181,649
Messages
5,931,217
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top