Comparison of Time Values in SELECT statements

[MontgomeryPete]

We are trying to set up a notification system to get patients ready for transportation to a clinic. The ECF staff needs to be notified 1 and 1/2 hours before the patient's appointment.

I have tried this approach:

In the query grid, calculate the "warning time:"

NoticeTime: [AppointmentTime]-(1.5/24). This gives a value 1 1/2 hours before the appointment.

Then I tried in the criteria section of that grid <Now()to act as a modifier for the SELECT statement.

Result: All appointments were returned, not just those within the time range.

Next: using (DateDiff("h",Now(),[NoticeTime]))/24
I used the result in an If statement to set a value equal to "1" and test for it on the criteria section of the query grid. The results were that it set a "1" for every case, regardless of the appointment time.

In this class, my goal is to use examples only in Access and not to use SQL or VB (this class is for nurses, medical technicians, etc.)

Does anyone have any thoughts on this problem?

Thanks and Happy New Year.

 

[dhookom]

Does your AppointmentTime contain both a date and time value? The Now() function returns both a date and time so if Appointment time is only the time part then you need to get them both aligned with time and date or just time.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MontgomeryPete]

Duane: Time functions are my weak point. I should have included a statement that let you know the select statement is set to Date() in the Date field to return only today's appointments.

I'm not sure how to align both on the basis of time only.

Thanks, Pete

 

[dhookom]

Why don't you tell us about your data fields and records. Then tell us how you expect to filter the records for displaying in your query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MontgomeryPete]

Duane, I guess the best way to express this is to outline the tables and show a copy of the SQL written by Access:

Tbl Patients: [PatientNo], [PatientName],[Physician],[Location],[Memo]

Tbl Appointments: [AppointmentNo],[PatientNo],[AppointmentDate],[AppointmentTime],[Clinic]

Qry Schedule: [AppointmentNo],[PatientNo],[AppointmentDate],[AppointmentTime],[Clinic]


SELECT Appointments.AppointmentNo, Appointments.PatientNo, Appointments.AppointmentDate, Appointments.AppointmentTime, [AppointmentTime]-(1.5/24) AS NoticeTime, Appointments.Clinic, Patients.PatientName, Patients.PatientLocation, Patients.PatientPhysician
FROM Patients INNER JOIN Appointments ON Patients.Patient No] = Appointments.PatientNo
WHERE (((Appointments.AppointmentDate)=Date()) AND (([AppointmentTime]-(1.5/24))<Now()));

The goal of the query is to populate a form that only shows patients going to (or arriving at) the clinic 1 1/2 hours ahead of the scheduled time.

Hope this clarifies things. I appreciate your interest.

Pete

 

[raskew]

Pete-

The dateadd() function should give you what you're after.
From the debug window:

appttime = #10:30#
warntime = dateadd("n", -90, appttime)
? warntime
9:00:00 AM

HTH - Bob

 

[dhookom]

As I stated earlier, Now() contains both the date and time so you could probably use:

WHERE (((Appointments.AppointmentDate)=Date()) AND (([AppointmentTime]-(1.5/24))<Time()));
Or
WHERE DateAdd("n",-90, AppointmentDate+AppointmentTime)<Now();


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MontgomeryPete]

Thanks Duane and Bob. The DateAdd worked fine, and I realized that I should have been using Time() and not Now() to compare specific times.

Have a great New Year

Pete