Parameter Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My orderDetail table has 3 date fields (all formated the same i.e. short
date). How, in the query design, do I exclude records from be reported in a
query if 1 date field is empty?
 
Add "Is Not Null" (no quotes) in the criteria row of the column where the
date in question is (in query design mode in the grid area).

Least I think that should work.

CW
 
My orderDetail table has 3 date fields (all formated the same i.e. short
date). How, in the query design, do I exclude records from be reported in a
query if 1 date field is empty?
Click to expand...

As criteria on that date field, write:
Is Not Null

If you wish to exclude all records where any one of the 3 date fields
is blank, then write:
Is Not Null
on the same criteria row for each date field.
 
If you want to exclude rows from the result set where *any* of the three date
columns is Null you can also make use of the fact that Nulls propagate in
arithmetic expressions, so Null + anything = Null, e.g.

SELECT *
FROM YourTable
WHERE Date1 + Date2 + Date3 IS NOT NULL;

Ken Sheridan
Stafford, England
 
Ken Sheridan said:
If you want to exclude rows from the result set where *any* of the three date
columns is Null you can also make use of the fact that Nulls propagate in
arithmetic expressions, so Null + anything = Null, e.g.

SELECT *
FROM YourTable
WHERE Date1 + Date2 + Date3 IS NOT NULL;
Click to expand...

But that would eliminate the ability of the query optimizer to use any indexes
on the fields. Much more efficient to use...

SELECT *
FROM YourTable
WHERE Date1 IS NOT NULL
AND Date2 IS NOT NULL
AND Date3 IS NOT NULL
 
Back
Top