IIF Statement for Date to show all if null
Experts,
I am having the same problem with a date field as the related question I solved for query search criteria. I am doing the exact same thing if the start date (defined as Text2 in the form) is null then it equals the Date Due in my query is shown. If it is not null then it is supposed to show the date between the Start Date (Text2) and the End Date (Text4). The true part of the equation works but the false part brings no results. See below:
IIf([Forms]![OOR]![Text2] Is Null,[Open Orders]![Date Due],Between [Forms]![OOR]![Text2] And [Forms]![OOR]![Text4])
I am having the same problem with a date field as the related question I solved for query search criteria. I am doing the exact same thing if the start date (defined as Text2 in the form) is null then it equals the Date Due in my query is shown. If it is not null then it is supposed to show the date between the Start Date (Text2) and the End Date (Text4). The true part of the equation works but the false part brings no results. See below:
IIf([Forms]![OOR]![Text2] Is Null,[Open Orders]![Date Due],Between [Forms]![OOR]![Text2] And [Forms]![OOR]![Text4])
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
You may need an explicit Date conversion to get the right range:
BETWEEN Cdate(NZ([Forms]![OOR]![Te
Also try this:
BETWEEN CDate(NZ([Forms]![OOR]![Te
BETWEEN Cdate(NZ([Forms]![OOR]![Te
Also try this:
BETWEEN CDate(NZ([Forms]![OOR]
If what is NULL, [Date Due]?
BETWEEN (Cdate(NZ([Forms]![OOR]![T
OR [Open Orders]![Date Due] IS NULL
BETWEEN (Cdate(NZ([Forms]![OOR]![T
OR [Open Orders]![Date Due] IS NULL
Sorry - I missed your post.
Verify that it is really giving you the right range -- by date, not alphabetically. If it is giving you the range alphabetically, then you need the CDates to explicitly oreder by dates.
Regarding the nulls-
NZ tests for nulls in the first argument and subsitutes the second argument if the first is null,
So NZ([Forms]![OOR]![Text2],[
and NZ([Forms]![OOR]![Text4],[
If they are both null, the range is between DateDue and DateDue -- which is the same thing as saying "equal to DateDue"
Does that clear or confuse it ? :-)
Verify that it is really giving you the right range -- by date, not alphabetically. If it is giving you the range alphabetically, then you need the CDates to explicitly oreder by dates.
Regarding the nulls-
NZ tests for nulls in the first argument and subsitutes the second argument if the first is null,
So NZ([Forms]![OOR]![Text2],[
and NZ([Forms]![OOR]![Text4],[
If they are both null, the range is between DateDue and DateDue -- which is the same thing as saying "equal to DateDue"
Does that clear or confuse it ? :-)
ASKER
Can you explain to me how that show the results if its null? I'm missing that.