Iif query

Hi, Dan.

You'd better clarify your question.
Do you want to find a records where time interval overlaps given time interval (i.e. to validate new record created)? This is rather simple.
Or you want to find an existing records where time interval overlaps?
This case try to run smthng like this:

Expand|Select|Wrap|Line Numbers

1. SELECT t1.*, t2.*
2. FROM [Your Table Name] AS t1 INNER JOIN [Your Table Name] AS t2 ON (t1.StartTime > t2.StartTime AND t1.StartTime < t2.EndTime) OR (t1.EndTime > t2.StartTime AND t1.EndTime < t2.EndTime);
3.  

Also posting table(s) metadata would be nice.

Here is an example of how to post table MetaData :
Table Name=tblStudent

Expand|Select|Wrap|Line Numbers

1. Field; Type; IndexInfo
2. StudentID; AutoNumber; PK
3. Family; String; FK
4. Name; String
5. University; String; FK
6. Mark; Numeric
7. LastAttendance; Date/Time

Hi, Dan. This way quite clear.

Try the following query. It retrieves overlapping time slot records.

Expand|Select|Wrap|Line Numbers

1. SELECT t1.*, t2.*
2. FROM [Your Table Name] AS t1 INNER JOIN [Your Table Name] AS t2 ON ((t1.StartTime > t2.StartTime AND t1.StartTime < t2.EndTime) OR (t1.EndTime > t2.StartTime AND t1.EndTime < t2.EndTime)) AND t1.Stylist = t2.Stylist AND t1.[Appt Date] = t2.[Appt Date];
3.  

• records with overlapping time periods are merged to one: this will require quite complicated data treatment (maybe via temporary table, or maybe via additional field of the existing one), and certainly depending on overlapping rate this may result in conglomerating of multiple records which maybe not what you want
• all records are retrieved with additional field with overlapping record ID or concatenated string consisting of overlapping records' IDs
• or maybe (not sure whether this is what you want) records returned reflect predefined time slots (9:00 - 10:00, 10:00 - 11:00, 11:00 - 12:00 etc) and records of your table overlapping with that predefined time intervals.

Hi, Dan.

Apologies not needed.
But some further clarification does.

I have several guesses of what recordset you want to get.

• records with overlapping time periods are merged to one: this will require quite complicated data treatment (maybe via temporary table, or maybe via additional field of the existing one), and certainly depending on overlapping rate this may result in conglomerating of multiple records which maybe not what you want
• all records are retrieved with additional field with overlapping record ID or concatenated string consisting of overlapping records' IDs
• or maybe (not sure whether this is what you want) records returned reflect predefined time slots (9:00 - 10:00, 10:00 - 11:00, 11:00 - 12:00 etc) and records of your table overlapping with that predefined time intervals.

Hmm.

So this situation is acceptable, nothing to restrict. Do you want just some kind of notification, i.e. textbox with overlapping records' IDs or a subform with overlapping records or button opening the subform? You may also have popup messagebox notifying user after new time interval was entered.

Regards,
Fish

Hi, Dan.

Put the following function to the form module and call it from BeforeUpdate event of the form, or from BeforeUpdate of each 4 controls mentioned. In the example controls have names the same as table fields. The function returns True if the interval entered does overlap no existing time slot, False if it does, Null if some of control(s) is(are) Null and validation could not be performed.

Expand|Select|Wrap|Line Numbers

1. Private Function ValidateTimeSlot() As Variant
2.  
3.     With Me
4.         If IsNull(.StartTime) Or IsNull(.EndTime) Or _
5.             IsNull(.Stylist) Or IsNull(.[Appt Date]) Then
6.             ValidateTimeSlot = Null
7.             Exit Function
8.         End If
9.         If DCount("ID", "[Your table name]", _
10.             "((StartTime>#" & .StartTime & "# And StartTime<#" & .EndTime & _
11.             "#) Or (EndTime>#" & .StartTime & "# And EndTime<#" & .EndTime & _
12.             "#) Or (StartTime<#" & .StartTime & "# And EndTime>#" & .EndTime & _
13.             "#)) AND Stylist=" & .Stylist & _
14.             " AND [Appt Date] = #" & .[Appt Date] & "#")<>0 Then
15.             MsgBox ("time interval bla bla bla")
16.             ValidateTimeSlot = False
17.         Else
18.             ValidateTimeSlot = True
19.         End If
20.     End With
21.  
22. End Function
23.  

Thanks FishVal

I will try this as soon as i can...


Dan

The function returns True if the interval entered does overlap no existing time slot, False if it does, Null if some of control(s) is(are) Null and validation could not be performed.

Hi, Dan.

Try this modified code. Note, comparisson operators changed in line#12.

Expand|Select|Wrap|Line Numbers

1. Private Function ValidateTimeSlot() As Variant
2.  
3.     With Me
4.         If IsNull(.StartTime) Or IsNull(.EndTime) Or _
5.             IsNull(.Stylist) Or IsNull(.[Appt Date]) Then
6.             ValidateTimeSlot = Null
7.             Exit Function
8.         End If
9.         If DCount("ID", "[Your table name]", _
10.             "((StartTime>#" & .StartTime & "# And StartTime<#" & .EndTime & _
11.             "#) Or (EndTime>#" & .StartTime & "# And EndTime<#" & .EndTime & _
12.             "#) Or (StartTime<=#" & .StartTime & "# And EndTime=>#" & .EndTime & _
13.             "#)) AND Stylist=" & .Stylist & _
14.             " AND [Appt Date] = #" & .[Appt Date] & "#")<>0 Then
15.             MsgBox ("time interval bla bla bla")
16.             ValidateTimeSlot = False
17.         Else
18.             ValidateTimeSlot = True
19.         End If
20.     End With
21.  
22. End Function
23.