Time field selection problem (1 Viewer)

[JohnPapa]

I have a table with several fields including the following 3 fields:

dtmAppoint Date = stores appointment date
dtmAppointTime = stores appointment time
lngAppointDoctorID = stores doctor's id for specific appointment

When I enter a new appointment, I want to know whether a specific combination of these 3 fields already exists. The doctor is actually a dentist and maybe another appointment has already been placed for the same dentist on a different chair. Creating a recordset as indicated below works but I am having some problems with comparing Time (dtmAppointTime). The following works but I need to extract Hour, Minute and Second and put it into string variable strTimeDum.

rstSearch.Open "SELECT * FROM tblAppointment " _
& " WHERE (clng(dtmAppointDate) = " & CLng(rst!dtmAppointDate) & " )"  _
[COLOR=red]& " AND (dtmAppointTime = #" & strTimeDum & "#)" _[/COLOR]
& " AND (lngAppointDoctorID = " & lngAppointDoctorID & ");", CurrentProject.connection, adOpenKeyset, adLockPessimistic

Can you suggest a way to retrieve the records that correspond to the
specific time or a simpler way to access the records of interest?

 

[CJ_London]

Before answering the question, are all appointments for the same period of time and all start on specific timepoints (e.g. always 15 mins and start on the hour,1/4 past, 1/2 past, 1/4 to)?

If the answer is no, we will also need to know the length of time of both the new appointment and existing appointments. Otherwise if a dentist has an existing appointment from 9 to 9:30, from the data provided a new appointment for 9:15 would appear to be OK.

 

[DavidAtWork]

If CJ's critical question turns out not to be a problem, then a simpler method would be to use a simple:
If DCount("*", "tblAppointment", and then your criteria) > 0 Then: msgbox "This date/time is already booked",vbokonly

 

[JohnPapa]

CJ and DavidAtWork thanks for your reply. The appointments are usually of the same duration but this does not pose a problem. Every dental work has a specified duration. For example, the Initial Exam may be defined to be 30 minutes and the Start time is 09:15. The software should assign an appointment for all open appointment slots from 09:15 until 09:45 (but not including 09:45).

You suggest to use

DCount("*", "tblAppointment", and then your criteria) > 0

The problem is the Criteria and how to compare Time. Also I need to know all potential conflicts and indicate these in a table to the user, so DCount in this case will not do.

At present I use something like

rst.Open "SELECT * FROM tblAppointment " _
& " WHERE (clng(dtmAppointDate) = " & CLng(dtmAppointDate) & " ) " _
[COLOR=red]& " AND dtmAppointTime >= #" & Format(dtmStartTime, "HH.NN.SS") & "# " _
& " AND dtmAppointTime < #" & Format(dtmFinishTime, "HH.NN.SS") & "# " _[/COLOR]
& " AND (lngChair = " & Me.lngChair & ")" _
& " ORDER BY dtmAppointTime ASC;", CurrentProject.connection, adOpenKeyset, adLockPessimistic

where the Start and Finish time criterai are shown in red.

 

[CJ_London]

The appointments are usually of the same duration but this does not pose a problem. Every dental work has a specified duration.

Looks like the answer is no, so the query 'needs to know' the durations for each dental work for each appointment - or are we to assume all appointments are 30 minutes?

 

[JohnPapa]

CJ let me give an example. An Orthodontist specifies his appointment slots to be every 15 minutes. If he starts at say 08:00, his appointments begin at 08:00, 08:15, 08:30, 08:45, 09:00, 09:15 etc. Also assume that a Braces appointment is three 15 minute units. If he starts the appointment at 08:00, he will need until (but not including) 08:45. In other words he will assign appointments 08:00, 08:15, 08:30.

If the user wants to arrange a a 3 Unit (3*15minute) appointment starting at 08:00, then the software will check ALL appointments starting at 08:00 until but not including appointment at 08:45, to ensure that they are empty and if they are empty, block the three appointment slots. This is where I needed to be able to check for the Time.
John

 

[jdraw]

John,

Instead of 3 units of 15 minutes each, suppose each unit was 14:59 seconds, or for simpler arithmetic 14.99 minutes.
It seems that would "fill" your slots, but not overlap to the 8:45 for example.

Just a thought.

 

[CJ_London]

Sorry to keep clarifying but I design a lot of appointment systems and I'm trying to get my head round the permutations.

The usual way to do this is to have a date/time field for the start of the appointment and a time field for the length of the appointment - though you might split the appointment start into separate date and time fields.

So I'm assuming from your example, if someone has booked a three unit appointment you will have 3 records, one for each unit as the booking

However it looks like I have digressed from your original post so suggest you try this on the masis my assumption is correct.

"

SELECT * FROM tblAppointment " _
& " WHERE (clng(dtmAppointDate) = " & CLng(dtmAppointDate) & " ) " _
& " AND CDbl(dtmAppointTime) >= CDbl(" & dtmStartTime & ") AND CDbl(dtmAppointTime)<CDbl(" & dtmFinishTime & ") & " AND (lngChair = " & Me.lngChair & ")" _
& " ORDER BY dtmAppointTime ASC;"

This assumes that dtmStartTime and dtmFinishTime are datetime datatypes in your form

 

[JohnPapa]

Good one jdraw. Such thinking would come from a dentist with a sense of humor!! It is accepted that the appointment slots are non-decimal.

CJ, I will try your code suggestion and you are right, a 3 unit appointmet would appear 3 times on the screen.

At present I am using,

rst.Open "SELECT * FROM tblAppointment " _
& " WHERE (clng(dtmAppointDate) = " & CLng(dtmAppointDate) & " ) " _
& " AND dtmAppointTime >= #" & Format(dtmStartTime, "HH.NN.SS") & "# " _
& " AND dtmAppointTime < #" & Format(dtmFinishTime, "HH.NN.SS") & "# " _
& " AND (lngChair = " & Me.lngChair & ")" _
& " ORDER BY dtmAppointTime ASC;", CurrentProject.connection, adOpenKeyset, adLockPessimistic

BTW, I have been told that it is bad programming to use clng on Date (which I am also using)

John

 

[CJ_London]

I have been told that it is bad programming to use clng on Date (which I am also using)

It can be if your date includes a time element - better to use CDbl. However I'm not aware of any reason why you shouldn't - the date value is stored as a decimal, date to the left of the dp and time (as a ratio of 24 hrs * 60mins * 60secs) to the right so 0.1 is 1 tenth =8640 seconds or 2hrs 24 mins.

 

[jdraw]

I wasn't joking, I was thinking of a way that you could book 3 consecutive 15 min time slots that didn't end on a 15 minute multiple. If you use standard arithmetic, unfortunately it would hit that 15 minute multiple. The dentist doesn't have to be concerned with the 14.99 -it would be a calculation behind the scenes.

I agree with CJ that normally you would have a date/time field for the start, and a duration field. If you preallocate time slots, then you would be checking to see if a proposed slot was available.
If the proposed slot was between a BookingStart and BookingStart+Duration, then you would have a conflict. If you want an appt on same day, you could show a list of BookedSlots and chose something that was Open (most direct approach). Or use the ProposedSlot, and some alternates( a little hit and miss but doable)

Since CJ has done several appointment schedules, he is more familiar with the concepts and implementations and would be best to comment.
Good luck with whatever you decide.