query a date range between two fields

[PAL]

I have a table with two date fields [Start Date] and [End Date].
The [End Date] is sometimes null.
Date Examples:
Category Start End
xyz 3/15/10 Null
xya 3/16/10 3/17/10
2324 3/8/10 3/19/10
ow can I get results like: Between 3/15/2010 and 3/19/2010 (or Null)
resulting in all start/end dates between the 15th and 19th (or Null)
Thanks

 

[Mio]

Hi,

The basic format to get this working is:

SELECT Table1.Gategory, Table1.Start, Table1.End
FROM Table1
WHERE ((((Table1.Start)>=#3/15/2010# And (Table1.Start)<=#3/19/2010#) Or
(Table1.Start) Is Null) AND (((Table1.End)>=#3/15/2010# And
(Table1.End)<=#3/19/2010#) Or (Table1.End) Is Null));

But if you use parameters in the query you will be avoiding editing the
actual query every time. Instead as you run the query you will be asked for
the StartDate and EndDate:

SELECT Table1.Gategory, Table1.Start, Table1.End
FROM Table1
WHERE (((Table1.Start)>=[StartDate] And (Table1.Start)<=[EndDate]) AND
((Table1.End)>=[StartDate] And (Table1.End)<=[EndDate])) OR (((Table1.Start)
Is Null) AND ((Table1.End)>=[StartDate] And (Table1.End)<=[EndDate])) OR
(((Table1.Start)>=[StartDate] And (Table1.Start)<=[EndDate]) AND
((Table1.End) Is Null)) OR (((Table1.Start) Is Null) AND ((Table1.End) Is
Null));

Developing futher you probably would like to add the reference that will
read the values from a form.

Best,

Mika