I've been trying all day to get a piece of VBA working and hope someone can relieve my suffering brain! I'm building a scheduling tool to help organise and run some meetings. I want to add agenda items from a form and check that the time that the user is entering does not overlap with any other times already made.
I have created a query that brings back all the agenda items for the meeting concerned, and contains the start and end times for it.
I'm then using the code below to try to interrogate the query and return values when the conditions are met, i.e. overlap. If the two checkclash variables are Null then it will write the agenda item to a table (the next bit of code that I need to write!).
I'm fairly sure that the method works as I've already got error handling that looks at whether the agenda item is within the start and end time of the meeting, and that works fine, it's just this bit that's causing me issues!
Checkclash1 = DLookup("[StartTime]", "qry_CurrentItems", "(" & NewStartTime < " & [Endtime]) AND (" & NewStartTime & " > [StartTime])")
CheckClash2 = DLookup("[StartTime]", "qry_CurrentItems", "(" & NewEndTime < " & [Endtime]) AND (" & NewEndTime & " > [StartTime])")
If IsNull(Checkclash1) And IsNull(CheckClash2) Then
MsgBox "Time OK"
If Not IsNull(Checkclash1) Or Not IsNull(CheckClash2) Then
MsgBox "Time Clashes"