Creating a Loop Statement comparing a time entered to a previous time range in the recordset

[LGrossman]

Hi All,

First of all I am not very familiar with creating loop statements and I have never really used forums before so please bare with me...



I have an Appointment table called AppointmentTbl which has the following (relevant) fields: -

AppointmentDate
StartTime
EndTime

I have a form called AppointmentFrm with the following text boxes: -

AppointmentDateTxt
StartTimeTxt
EndTimeTxt

What I would like to do is when the user enters a Date then the Start Time, it looks though the previous records and if a previous date matches AND the Start Time is within a previous Start and End Time then warn the user there is a conflict.

example:

User enters 24/03/2022 and Start Time 10:30

There is a previous record that has an appointment: 24/03/2022 with a Start Time of 10:00 and an End Time of 11:00.

What the user has entered is the same date and 10:30 is within 10:00 and 11:00 so the user needs to be warned (just a msgbox with a continue or not message)

I assume I need a loop statement and I would welcome any help as it's driving mee crazy.

Thank you in advance and I hope I've explained it ok.

[Micron]

No looping required. Try building a query that returns records
Where Date = Forms!AppointmentFrm.AppointmentDateTxt And StartTime > Forms!AppointmentFrm.StartTimeTxt And StartTime < Forms!AppointmentFrm.EndTimeTxt
(could use Between operator instead).

or you can try using that logic as the Where part of the DLookup function.

If you can get that to work, you can move the query into your code if that's where you want it, using one of a number of methods. Or you can just try doing the lookup in your current code. If you need help with that, post the code for the procedure. Please enclose more than a few lines of code within code tags (use # button on posting toolbar).

[LGrossman]

Thanks, I'm out of the office at the moment but will try in the morning.

[LGrossman]

I've had a closer look at your suggestions and I like the look of using the DLookup function.

I have coded very simple DLookup statements that retrieve a single value but never a more complicated crtiteria.

I know I'm asking a lot but can you assist in the structuring of such a statement?

Thanks

[Micron]

NP. Need to make sure of something first.

when the user enters a Date then the Start Time, it looks though the previous records and if a previous date matches AND

If I'm creating the appointments for me or anyone else, they are always for either me or always that other person - not both?
If instead some appointments are for you and some for another person, you have a different situation. Discovering that there is an appointment made on a particular date doesn't help if some are for you and some are not.

If you have any code related to this issue, it might make a good starting point.

[LGrossman]

Sorry I didn't make that bit clear...

This is for one person so the check just needs to be: When creating a new appointment, it will notify the user if the start time conflicts with previous appointments times for that date.

I don't have any code for it currently but I thought the DLookup statement would go in the After Update event of the StartTimeTXT?

[Micron]

The new info and further thought is making me think that you will need a loop after all. That is because there could be several appointments on a given date that is the same as the new proposed appointment, and they all need to be checked against the proposed date. DLookup can only retrieve one record. If no one beats me to it, I'll start over and see if I can come up with something using just your table/field/control info provided. Or you could copy the db, compact then zip it and post it here if the data isn't real sensitive.

EDIT - will need to know what data type your time fields are and what the data looks like. Could be text, could be date with time, could be time portion only, or maybe something else.

[LGrossman]

That is very kind of you...

If you wouldn't mind using the table/field/control info I provided, that would be great thanks.

[Micron]

Sorry if I'm asking too many questions, but options come to mind as I write this. You only want to know if the chosen time falls between the start and end times of an appointment on the proposed day, and nothing else? User would then do what, just pick another time and hope for the best, or go looking for where the conflicts are? What if I could include the offending start time (harder to return more than just the first one) or report how many appointments where this condition occurs (could there even be more than one?). Or is there something else that would help to select a viable time?

P.S. you probably didn't see my edited post asking about your time field and values.

[Micron]

Going to post what I have so I can hopefully close this out. You can try this in your event (not sure what that is - AfterUpdate of the StartTime textbox?). If that is the case, the 'successful' message may not be adequate. Assumes your time fields are compatible with my chosen variable type (Double).

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strSql As String
Dim dblEnd As Double, dblStart As Double, dblNewTime As Double '??
Dim i As Integer

On Error GoTo errHandler

strSql = "SELECT AppointmentDate, StartTime, EndTime FROM AppointmentTbl WHERE"
strSql = strSql & " AppointmentDate = #" & Me.AppointmentDateTxt & "#"

Set db = CurrentDb
'open recordset based on strSql
Set rs = db.OpenRecordset(strSql)
If Not (rs.BOF And rs.EOF) Then 'if Not, then there are records
   dblNewTime = Me.StartTimeTxt
   Do Until rs.EOF
      dblStart = rs.Fields("StartTime")
      dblEnd = rs.Fields("EndTime")
      If dblNewTime > dblStart And dblNewTime < dblEnd Then i = i + 1
      rs.MoveNext
   Loop
End If

If i > 0 Then
  MsgBox "There are " & i & " conflicting appointments for that time on " & Me.AppointmentDateTxt
Else
  MsgBox "Appointment creation successful"
End If

exitHere:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

[LGrossman]

That worked a treat, thank you so much for your help.

[Micron]

Pleasantly surprised - usually at least one assumption is wrong! Glad I could help & sorry I missed the mark at first.

[LGrossman]

No need to apologise, you saved me hours of trying to figure it out.

Thanks again!

[LGrossman]

Pleasantly surprised - usually at least one assumption is wrong! Glad I could help & sorry I missed the mark at first.

Hi Again, Although I thought this issue was solved, I've got another slight problem with it...

The Code you gave me works great but there seems to be a date format problem. It looks like an issue between the SQL date format and regional date format. for example: -

I have a previous appointment of say 26/03/2022 @ 10:00, when I create a new appointment on 26/03/2022 @ 10:30, it correctly tells me there is a conflict. However if have a previous appointment of the 12th or before, it doesn't see the conflict.

Any Idea how I force the date formats to match using the code you gave me?

Thanks.

[Micron]

I overlooked that your date format is not US. You will probably have to adapt the second sql construct line according to #2 here:
http://allenbrowne.com/ser-36.html

You may want to use the function shown there since you'll likely be faced with the problem often. Hopefully that is the issue, but I can't tell if by the 12th you mean the month or the day. Such a simple thing but what can you say about a world that can't even standardize the date format?