Searching dates

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

Guest

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.
 
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
 
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])
 
Back
Top