problem with date in query

[Sunil Korah]

Hi,

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

SELECT Patients.PID, Patients.Lastna me, Patients.FirstN ame,
Patients.DateOf Visit, Patients.ToRepo rt
FROM Patients
WHERE (((Patients.Dat eOfVisit)>=GetS tartDate() And
(Patients.DateO fVisit)<=GetEnd Date()))
ORDER BY Patients.DateOf Visit;

(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 "DateOfVisi t" 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]

Re: problem with date in query

On 29 Sep 2004 11:33:52 -0700, Sunil Korah wrote:
[color=blue]
> Hi,
>
> My table contains a date field "DateOfVisi t" and I have the following
> query
>
> SELECT Patients.PID, Patients.Lastna me, Patients.FirstN ame,
> Patients.DateOf Visit, Patients.ToRepo rt
> FROM Patients
> WHERE (((Patients.Dat eOfVisit)>=GetS tartDate() And
> (Patients.DateO fVisit)<=GetEnd Date()))
> ORDER BY Patients.DateOf Visit;
>
> (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 "DateOfVisi t" 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[/color]

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:[color=blue]
>= Date() and <= Date()[/color]

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