Searching dates

M

Mark Butler

I have a list of admissions that includes a start date and an end date. I am
trying to produce a list to show the number of members that are current.
I have tried searching for specific dates which works fine but it doesn't
show any dates between start data and end dates only those that fall in the
field that i am searching which is useless for planning how many places are
still available for booking.
 
R

raskew via AccessMonster.com

Hi -
Here's a sample query which returns active records, i.e. either the end date
is null or the end date > date(). Hopefully you can adapt it to your data.

SELECT
tblResAdmissions.AdmissionID
, tblResAdmissions.DteAdmitted
, tblResAdmissions.DteRelease
FROM
tblResAdmissions
WHERE
((Not (tblResAdmissions.DteAdmitted) Is Null
AND
(tblResAdmissions.DteAdmitted)<Date())
AND
((tblResAdmissions.DteRelease) Is Null
OR
(tblResAdmissions.DteRelease)>Date()));

HTH - Bob
 
K

KARL DEWEY

Not sure of your table structure but try this untested.
In the design view add an output field Today: Date()
Use this as criteria --
Between [start date] AND IIF([end date] Is Null, Date(), [end date])
 
Top