Dlookup / Dcount ? Stumped!!!

[Taff82]

Hi all,

I have a form with the following fields:-

StartTime = Format dd/mm/yyyy hh:nn:ss
ExpFinishTime = Format dd/mm/yyyy hh:nn:ss
Dentist = Number

StartTime is bound to my table, ExpFinishTime is calculated in the forms underlying query.

Before the record updates I am trying to check if anyone already has an appointment scheduled in during that particular time for that particular dentist. So if the record being added has a starttime between those two times for that dentist, throw up a message box.

Thanks for any help.

Taff

 

[jaydeebe]

This is quite complicated. I have a working booking system that uses dates without times. The way to check for collisions is to use a recordset check like in this thread -

http://www.tek-tips.com/viewthread.cfm?qid=834753&page=2

or

I have used three queries to achieve a similar result.

You can reduce the records to query by marking the appointments as complete. In this first query i get the records that haven't been returned.

SELECT tblbookings.UserID, tblbookings.ResID, tblbookings.StartDate, tblbookings.EndDate, tblbookings.Status
FROM tblbookings
WHERE (((tblbookings.Status)="On loan"));

this second query checks for overlapping bookings

SELECT tblUsers.Firstname, tblUsers.Surname, tblUsers.TelExt, qryNoReturn.UserID, qryNoReturn.ResID, qryNoReturn.StartDate, qryNoReturn.EndDate, qryNoReturn.Status AS Expr1
FROM qryNoReturn INNER JOIN tblUsers ON qryNoReturn.UserID = tblUsers.UserID
WHERE ((([Forms]![frmbooking]![txtStartDate]-1) Between [tblbookings]![StartDate] And [tblbookings]![EndDate])) OR ((([Forms]![frmbooking]![txtEndDate]) Between [tblbookings]![StartDate] And [tblbookings]![EndDate])) OR ((([tblbookings]![StartDate]) Between [Forms]![frmbooking]![txtStartDate]-1 And [Forms]![frmbooking]![txtEndDate]));

the third returns resources that are available.

SELECT tblResources.ResID, qryCollision.Surname, qryCollision.Firstname, qryCollision.TelExt, qryCollision.StartDate, qryCollision.EndDate
FROM qryCollision RIGHT JOIN tblResources ON qryCollision.ResID = tblResources.ResID
WHERE (((tblResources.ResID) Not In ([qryCollision]![ResID])));

 

[Taff82]

Thanks jaydeebe,

I will have a play about and let you know how I get on.

Much Appreciated.

Taff.