By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,587 Members | 623 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,587 IT Pros & Developers. It's quick & easy.

Iif query

P: 45
Hi All

Trying to write a query using an "Iif" statement.

The database I am creating is for hair salon appointment managment.

The thing is, stylists can book more than one client in a given slot. Eg 10:00 - 11:00 they have Client "A" and 10:30 - 11:00 they have Client "B".

I would like to write a query where if they were to book another client in - it would flag and say another appointment has been booked.

I cant figure out how to do this because I will be refering to the "same thing"

If [StartTime] is between ([StartTime] AND [EndTime] - of another record) Then ......


Can you please help??



Many Thanks


Dan
Oct 8 '07 #1
Share this Question
Share on Google+
16 Replies


FishVal
Expert 2.5K+
P: 2,653
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
Oct 8 '07 #2

P: 45
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 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
Oct 8 '07 #3

FishVal
Expert 2.5K+
P: 2,653
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.  
Oct 8 '07 #4

P: 45
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.  
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
Oct 8 '07 #5

FishVal
Expert 2.5K+
P: 2,653
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.
Oct 9 '07 #6

P: 45
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
Oct 9 '07 #7

FishVal
Expert 2.5K+
P: 2,653
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
Oct 9 '07 #8

P: 45
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
Oct 9 '07 #9

FishVal
Expert 2.5K+
P: 2,653
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.  
Oct 9 '07 #10

P: 45
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
Oct 9 '07 #11

P: 45
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 ValidateTimeSlot()" in the form before update

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

Expand|Select|Wrap|Line Numbers
  1.  Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  
  3. Run ValidateTimeSlot()
  4.  
  5. End Sub
It then highlights the validatetimeslot in the above code.


Thanks


Dan
Oct 10 '07 #12

FishVal
Expert 2.5K+
P: 2,653
Hi, Dan.

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

As I've posted
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Form_BeforeUpdate(Cancel As Integer)
  3.  
  4.     Dim varValidationResult as Variant
  5.  
  6.     varValidationResult = ValidateTimeSlot()
  7.  
  8.     If IsNull(varValidationResult) Then
  9.         MsgBox("Field(s) required for validation is/are empty")
  10.         Cancel = True
  11.     Else
  12.         If varValidationResult = False Then
  13.             MsgBox("Time interval invalid bla bla bla")
  14.             Cancel = True
  15.         Else
  16.             Cancel = False
  17.         End If
  18.     End If
  19.  
  20. End Sub
Oct 10 '07 #13

P: 45
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
Oct 12 '07 #14

FishVal
Expert 2.5K+
P: 2,653
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.  
Oct 12 '07 #15

P: 45
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.  
Thanks FishVal!

Its working great now! Thanks ever so much for your help!

Thanks

Dan
Oct 14 '07 #16

FishVal
Expert 2.5K+
P: 2,653
You are welcome, Dan.

Good luck.
Oct 14 '07 #17

Post your reply

Sign in to post your reply or Sign up for a free account.