parameterized query with null input ?

[Dorian]

Is there any way to code a parameterized query to act when nothing is entered?
e.g.
SELECT * FROM myTable WHERE myStatus = [Status] AND myDate BETWEEN [From]
AND [To];

I'd like the user to be able to just hit return to select rows for any
status and also to be able to omit dates to choose all dates.

 

[Clifford Bass]

HI Dorian,

Try:

SELECT * FROM myTable WHERE myStatus = Nz([Status], [myStatus]) AND myDate
BETWEEN Nz([From], #1/1/1900#) AND Nz([To], #12/31/9999#);

Clifford Bass

 

[Allen Browne]

You can fudge it along these lines:

PARAMETERS FromDate DateTime, ToDate DateTime;
SELECT myTable.*
FROM myTable
WHERE (myStatus Is Null OR myStatus Like
IIf([Status] Is Null, "*", [Status]))
AND (myDate Is Null OR myDate >=
IIf([FromDate] Is Null, #1/1/1900#, [FromDate]))
AND (myDate Is Null OR myDate <=
IIf([ToDate] Is Null, #1/1/9999#, [ToDate]))

Notes:
a) From and To are reserved words, so I renamed the parameters.

b) Declare the date parameters, so Access gets the data type right.

c) Do not declare the text parameter:
http://allenbrowne.com/bug-13.html

d) The test for nulls matters if you want to include nulls in your result.
The criterion:
Like "*"
excludes nulls.

e) It would be much better interface to use a form where the user can enter
the critiera, and much more efficient to build the criteria from only those
boxes where the user actually entered something. For an example of how to do
that, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html