IIf command with Between

[jmc014]

Hi, I could use a little help if anyone's got the time,

I've got a simple query that I'm trying to get some simple results from:

If the Main Form [Date_Frm] field is null, then return all the date form the table.
ELSE, if the [Date_Frm] field contains a date, return the Between dates criteria.

Here is the criteria that I'm using in my query:

IIf(IsNull([Forms]![Main_Form]![Search_GSIS]![Date_Frm]);[ArrivalSCH];Between [Forms]![Main_Form]![Search_GSIS]![Fecha_Frm] And [Forms]![Main_Form]![Search_GSIS]![Date_To])

Issue:
If the Date_Frm field is blank, no problem, the results are showing. But when the Date_Frm field contains a valid date, the results from my query are always blank.

Note:
Using the "Between [Forms]![Main_Form]![Search_GSIS]![Fecha_Frm] And [Forms]![Main_Form]![Search_GSIS]![Date_To]" criteira WITHOUT IIF works fine!

Thanks for anyone's time

 

[dhookom]

I would try use the Nz() function like:

Between Nz([Forms]![Main_Form]![Search_GSIS]![Date_Frm],#1/1/1900#) And Nz([Forms]![Main_Form]![Search_GSIS]![Date_To],#12/31/3000#)

This assumes all dates will be between 1/1/1900 and 12/31/3000. You can substitute other dates or expressions.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016