Query to locate records within Date Range 2

[Accel45]

I have a database which tracks persons within a program. The length of the program is not fixed.

tblPerson has fields for demographic information as well as a program startdate field and a program enddate field.

I am attempting to create a query that retrieves records that fall within a given year.
For instance: What people were in the program for even 1 day during 2007?

The problem I’m having is with those records that have start dates before 2007 and end dates after 2007 (or no end date yet) but were in the program during 2007.

Any help or suggestions on how to retrieve the records would be appreciated.

Thanks
Accel45

 

[randy700]

How about...

WHERE StartDate Between #01/01/2007# And #12/31/2007#
OR EndDate Between #01/01/2007# And #12/31/2007#
OR (StartDate < #01/01/2007# AND EndDate > #12/31/2007#)
OR (StartDate < #01/01/2007# AND IsNull(EndDate))

Note: not tested


Randy

 

[MajP]

You can also use the complement of those included in the period.

SELECT tblDates.dtmStart, 
tblDates.dtmEnd 
FROM tblDates
WHERE NOT (((tblDates.dtmStart)>#12/31/2007#) OR ((nz([dtmEnd],#12/31/2007#))<#1/1/2007#));