IIF question

[Guest]

I am trying to use an expression to pull data based on a date range. If the
date range is null, then I want the query to pull all records.

I have a form where the users enters a date range. [date1] and [date2]

So far I have this expression but it doesn't pull anything when a date range
is entered.

Like iif([form]![maintable]![date1] is null,"*",Between
[form]![maintable]![date1] and [form]![maintable]![date2])

what am i doing wrong? please help. thanks

 

[Guest]

In the query's WHERE clause test for the range OR the parameters being NULL,
e.g.

PARAMETERS
[Forms]![maintable]![date1] DATETIME,
[Forms]![maintable]![date2] DATETIME;
SELECT *
FROM YourTable
WHERE YourDate BETWEEN [Forms]![maintable]![date1]
AND [Forms]![maintable]![date2]
OR [Forms]![maintable]![date1] IS NULL;

Note that you really only have to test for one of the parameters being NULL.
Also its prudent to declare date/time parameters as otherwise a date in
short date format could be interpreted as an arithmetical expression rather
than a date value and give the wrong results.

Ken Sheridan
Stafford, England

 

[Guest]

You can't place the Between inside the function. Try:
Between Nz([form]![maintable]![date1], [YourDateField]) AND
Nz([form]![maintable]![date2], [YourDateField])

Replace [YourDateField] with your date field.

 

[Guest]

Thank you very much. it worked perfectly.

You can't place the Between inside the function. Try:
Between Nz([form]![maintable]![date1], [YourDateField]) AND
Nz([form]![maintable]![date2], [YourDateField])

Replace [YourDateField] with your date field.

--
Duane Hookom
Microsoft Access MVP

I am trying to use an expression to pull data based on a date range. If the
date range is null, then I want the query to pull all records.

I have a form where the users enters a date range. [date1] and [date2]

So far I have this expression but it doesn't pull anything when a date range
is entered.

Like iif([form]![maintable]![date1] is null,"*",Between
[form]![maintable]![date1] and [form]![maintable]![date2])

what am i doing wrong? please help. thanks