optional parameter query

[shadowsong]

I have a form with start date and end date parameters that the user
can input to run a query. I would like the query to be set up so that
null parameters means to ignore that constraint. That is to say:

if a is not null and b is not null
then c between a and b
else if a is not null and b is null
then c >= a
else if a is null and b is not null
then c <= b
else do nothing and move on

except using iif because if/then/else doesn't work in sql as far as i
know.

What is the real code for "do nothing and move on?" I tried using
wildcards (else a = *), but it didn't work, probably because I'm using
the wrong wildcard for date formats.

Here's the query as it stands, with the date parameters required:

SELECT DATA.DLRNUM, DATA.DLRNAME, DATA.SALESNAME, DATA.MODEL, DATA.SN,
DATA.SOLD
FROM DATA
WHERE (FORMS![SALESPERSON OUTPUT]!DLRNUM is null or DATA.DLRNUM=FORMS!
[SALESPERSON OUTPUT]!DLRNUM)
And (FORMS![SALESPERSON OUTPUT]!SALESNAME is null or
DATA.SALESNAME=FORMS![SALESPERSON OUTPUT]!SALESNAME)
And DATA.SOLD Between FORMS![SALESPERSON OUTPUT]!STARTDATE And FORMS!
[SALESPERSON OUTPUT]!ENDDATE;

 

[Allen Browne]

Where is this query headed? The most efficient solution would be to build a
filter string from only the boxes where the user entered a value. You can
then use that string as the Filter for a form, or the WhereCondition for
OpenReport.

Details in Method 2 of this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

If that is not suitable, you will need to drop the Between operator and user
two phrases in the WHERE clause, as you did for the other 2 criteria:

WHERE (([FORMS]![SALESPERSON OUTPUT]![DLRNUM] is null)
or (DATA.DLRNUM = [FORMS]![SALESPERSON OUTPUT]![DLRNUM]))
And (([FORMS]![SALESPERSON OUTPUT]![SALESNAME] is null)
or (DATA.SALESNAME = [FORMS]![SALESPERSON OUTPUT]![SALESNAME]))
And (([FORMS]![SALESPERSON OUTPUT]![STARTDATE] is null)
or (DATA.SOLD >= [FORMS]![SALESPERSON OUTPUT]![STARTDATE]))
And (([FORMS]![SALESPERSON OUTPUT]![ENDDATE] is null)
or (DATA.SOLD < ([FORMS]![SALESPERSON OUTPUT]![ENDDATE] + 1)))