problem with date in query

[Sunil Korah]

Hi,

My table contains a date field "DateOfVisit" and I have the following
query

SELECT Patients.PID, Patients.Lastname, Patients.FirstName,
Patients.DateOfVisit, Patients.ToReport
FROM Patients
WHERE (((Patients.DateOfVisit)>=GetStartDate() And
(Patients.DateOfVisit)<=GetEndDate()))
ORDER BY Patients.DateOfVisit;

(GetStartDate() and GetEndDate() are for getting the dates which are
held in public variables into the SQL.)

When I set the start and end date variables to Date() I expect to see
the entries with the "DateOfVisit" as today, but they dont come up. If
I set the start date as an earlier date they come up along with other
records. I cant understand why?

Sunil Korah

[fredg]

On 29 Sep 2004 11:33:52 -0700, Sunil Korah wrote:

Hi,

My table contains a date field "DateOfVisit" and I have the following
query

SELECT Patients.PID, Patients.Lastname, Patients.FirstName,
Patients.DateOfVisit, Patients.ToReport
FROM Patients
WHERE (((Patients.DateOfVisit)>=GetStartDate() And
(Patients.DateOfVisit)<=GetEndDate()))
ORDER BY Patients.DateOfVisit;

(GetStartDate() and GetEndDate() are for getting the dates which are
held in public variables into the SQL.)

When I set the start and end date variables to Date() I expect to see
the entries with the "DateOfVisit" as today, but they dont come up. If
I set the start date as an earlier date they come up along with other
records. I cant understand why?

Sunil Korah
Your DateOfVisit field is storing a date AND a time value.
No records will meet the <= Date() part of the criteria unless the
time value is exactly midnight.

Change the data (using an Update query) to just the date value, or
change the query criteria to >=Date() and <= Date() +1

Or .... add a new column to the query grid.
Exp:Int([DateOfVisit])

Set the criteria for this column to:= Date() and <= Date()

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.