Iif query

**FishVal** · Oct 8 '07, 06:12 PM

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:
[code=sql]
SELECT t1.*, t2.*
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);
[/code]
Also posting table(s) metadata would be nice.

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

Code:

[i]Field; Type; IndexInfo[/i]
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

**NCRStinks** · Oct 8 '07, 07:07 PM

Originally posted by FishVal

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:
[code=sql]
SELECT t1.*, t2.*
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);
[/code]
Also posting table(s) metadata would be nice.

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

Code:

[i]Field; Type; IndexInfo[/i]
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Hi FishVal

Table Structure:

ID, AutoNumber
Customer. Number
Stylist, Number
Appt Date, Date/Time
StartTime, Date/Time
EndTime, Date/Time
Descr., Text

I am trying to design a report, in an "outlook" style, so I am using VB to position and size the appointments.

The reason for the query is to find the duplicate, and to alter the "left" position, effectively making 2 columns so the data does not overlap on the report. This is being done by having a normal left value, and if it is a duplicate using a higher figure.

Hoping that I have clarified.

Thanks

Dan

**FishVal** · Oct 8 '07, 07:30 PM

Hi, Dan. This way quite clear.

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

[code=sql]
SELECT t1.*, t2.*
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];
[/code]

**NCRStinks** · Oct 8 '07, 07:59 PM

Originally posted by FishVal

Hi, Dan. This way quite clear.

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

[code=sql]
SELECT t1.*, t2.*
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];
[/code]

Hi FishVal

Sorry to be a pain, is there possibly a way of showing all the records in a table but marking one of the duplicates in some way?

At the moment the "normal" appointments are given a numerical value to give a left value on the report, the desired outcome would be - if a duplicate to do normal left value + some extra.... so that the appointments do not overlap when producing the report.

The suggestion certainly works for getting the duplicate values, its just putting that in a usable form.

Again appologies!

Dan

**FishVal** · Oct 9 '07, 05:00 PM

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.

**NCRStinks** · Oct 9 '07, 05:30 PM

Originally posted by FishVal

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.

Hi FishVal

I've been mulling over this one whilst at work today...

I was thinking of some sort of validation when booking an appointment. If give a stylist say 2 chairs. When booking an appointment the default will be "Chair 1".

But, if an appointment already exists during the timeslot of the new appointment perhaps a popup box saying Change to "Chair 2" or change Date/Time.

This seems like a slightly more logical and easier idea compared to my previous thoughts?

Thanks

Dan

**FishVal** · Oct 9 '07, 06:06 PM

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

**NCRStinks** · Oct 9 '07, 06:16 PM

Originally posted by FishVal

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 FishVal

Yes I think so.

Just a message box to notify the user, that Chair 1 is booked for specified time, please book to "chair 2" or select another date/time.

Any ideas how to perform this?

Im feeling really wet, I have tried sinking my head in books - but no good - nothing seems to go in depth about validation - and if it does it doesnt go near date/time.

Thanks

Dan

**FishVal** · Oct 9 '07, 06:55 PM

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.

Code:

Private Function ValidateTimeSlot() As Variant
    
    With Me
        If IsNull(.StartTime) Or IsNull(.EndTime) Or _
            IsNull(.Stylist) Or IsNull(.[Appt Date]) Then
            ValidateTimeSlot = Null
            Exit Function
        End If
        If DCount("ID", "[Your table name]", _
            "((StartTime>#" & .StartTime & "# And StartTime<#" & .EndTime & _
            "#) Or (EndTime>#" & .StartTime & "# And EndTime<#" & .EndTime & _
            "#) Or (StartTime<#" & .StartTime & "# And EndTime>#" & .EndTime & _
            "#)) AND Stylist=" & .Stylist & _
            " AND [Appt Date] = #" & .[Appt Date] & "#")<>0 Then
            MsgBox ("time interval bla bla bla")
            ValidateTimeSlot = False
        Else
            ValidateTimeSlot = True
        End If
    End With
    
End Function

**NCRStinks** · Oct 9 '07, 07:49 PM

Originally posted by FishVal

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.

Code:

Private Function ValidateTimeSlot() As Variant
    
    With Me
        If IsNull(.StartTime) Or IsNull(.EndTime) Or _
            IsNull(.Stylist) Or IsNull(.[Appt Date]) Then
            ValidateTimeSlot = Null
            Exit Function
        End If
        If DCount("ID", "[Your table name]", _
            "((StartTime>#" & .StartTime & "# And StartTime<#" & .EndTime & _
            "#) Or (EndTime>#" & .StartTime & "# And EndTime<#" & .EndTime & _
            "#) Or (StartTime<#" & .StartTime & "# And EndTime>#" & .EndTime & _
            "#)) AND Stylist=" & .Stylist & _
            " AND [Appt Date] = #" & .[Appt Date] & "#")<>0 Then
            MsgBox ("time interval bla bla bla")
            ValidateTimeSlot = False
        Else
            ValidateTimeSlot = True
        End If
    End With
    
End Function

Thanks FishVal

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

Dan

**NCRStinks** · Oct 10 '07, 09:15 PM

Originally posted by NCRStinks

Thanks FishVal

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

Dan

Hi FishVal

Not having much luck with this - I know I must be doing something wrong!

I have added the function to the form, and have added a "run ValidateTimeSlo t()" in the form before update

It is coming up with an error cant find the procedure "True"

Code:

 Private Sub Form_BeforeUpdate(Cancel As Integer)

Run ValidateTimeSlot()
            
End Sub

It then highlights the validatetimeslo t in the above code.

Thanks

Dan

**FishVal** · Oct 10 '07, 09:35 PM

Hi, Dan.

:) Take a look at the code. You try to run what ValidateTimeSlo t() returns. And it returns True, this gives me an evidence that the function at least doesn't fail.

As I've posted

Originally posted by me in msg#10

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.

so why not to use it's returning value to perform some validation actions.

e.g.

Code:

 
Private Sub Form_BeforeUpdate(Cancel As Integer)

    Dim varValidationResult as Variant

    varValidationResult = ValidateTimeSlot()

    If IsNull(varValidationResult) Then
        MsgBox("Field(s) required for validation is/are empty")
        Cancel = True
    Else
        If varValidationResult = False Then
            MsgBox("Time interval invalid bla bla bla")
            Cancel = True
        Else
            Cancel = False
        End If
    End If

End Sub

**NCRStinks** · Oct 12 '07, 05:16 PM

Thanks FishVal

That works great. Only problem being is that exact duplicates can be created.

I have tried looking into the DCount function as you have demonstrated, but I cant get this to work.

Thanks

Dan

**FishVal** · Oct 12 '07, 05:30 PM

Hi, Dan.

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

Code:

Private Function ValidateTimeSlot() As Variant
    
    With Me
        If IsNull(.StartTime) Or IsNull(.EndTime) Or _
            IsNull(.Stylist) Or IsNull(.[Appt Date]) Then
            ValidateTimeSlot = Null
            Exit Function
        End If
        If DCount("ID", "[Your table name]", _
            "((StartTime>#" & .StartTime & "# And StartTime<#" & .EndTime & _
            "#) Or (EndTime>#" & .StartTime & "# And EndTime<#" & .EndTime & _
            "#) Or (StartTime<=#" & .StartTime & "# And EndTime=>#" & .EndTime & _
            "#)) AND Stylist=" & .Stylist & _
            " AND [Appt Date] = #" & .[Appt Date] & "#")<>0 Then
            MsgBox ("time interval bla bla bla")
            ValidateTimeSlot = False
        Else
            ValidateTimeSlot = True
        End If
    End With
    
End Function

Iif query

Iif query

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment