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
16 2748
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: -
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);
-
Also posting table(s) metadata would be nice.
Here is an example of how to post table MetaData : Table Name=tblStudent - Field; Type; IndexInfo
-
StudentID; AutoNumber; PK
-
Family; String; FK
-
Name; String
-
University; String; FK
-
Mark; Numeric
-
LastAttendance; Date/Time
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: -
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);
-
Also posting table(s) metadata would be nice.
Here is an example of how to post table MetaData : Table Name=tblStudent - Field; Type; IndexInfo
-
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
Hi, Dan. This way quite clear.
Try the following query. It retrieves overlapping time slot records. -
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];
-
Hi, Dan. This way quite clear.
Try the following query. It retrieves overlapping time slot records. -
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];
-
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
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, 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
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
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
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. -
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
-
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. -
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
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" - Private Sub Form_BeforeUpdate(Cancel As Integer)
-
-
Run ValidateTimeSlot()
-
-
End Sub
It then highlights the validatetimeslot in the above code.
Thanks
Dan
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. -
-
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
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
Hi, Dan.
Try this modified code. Note, comparisson operators changed in line#12. -
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
-
Hi, Dan.
Try this modified code. Note, comparisson operators changed in line#12. -
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!
Its working great now! Thanks ever so much for your help!
Thanks
Dan
You are welcome, Dan.
Good luck.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: jaysonsch |
last post by:
Hello!
I am having some problems with a database query that I am trying to do.
I am trying to develop a way to search a database for an entry and
then edit the existing values. Upon submit, the...
|
by: shank |
last post by:
1) I'm getting this error: Syntax error (missing operator) in query
expression on the below statement. Can I get some advice.
2) I searched ASPFAQ and came up blank. Where can find the "rules"...
|
by: netpurpose |
last post by:
I need to extract data from this table to find the lowest prices of
each product as of today. The product will be listed/grouped by the
name only, discarding the product code - I use...
|
by: Harvey |
last post by:
Hi,
I try to write an asp query form that lets client search any text-string and
display all pages in my web server that contain the text. I have IIS 6.0 on a
server 2003. The MSDN site says...
|
by: Diamondback |
last post by:
I have two tables, WIDGETS and VERSIONS. The WIDGETS table has
descriptive information about the widgets while the VERSIONS table
contains IDs relating to different iterations of those widgets...
|
by: Dave Thomas |
last post by:
If I have a table set up like this:
Name | VARCHAR
Email | VARCHAR
Age | TINYINT | NULL (Default: NULL)
And I want the user to enter his or her name, email, and age - but AGE
is optional.
...
|
by: starace |
last post by:
I have designed a form that has 5 different list boxes where the
selections within each are used as criteria in building a dynamic
query. Some boxes are set for multiple selections but these list...
|
by: jjturon |
last post by:
Can anyone help me??
I am trying to pass a Select Query variable to a table using Dlookup
and return the value to same select query but to another field.
Ex.
SalesManID ...
|
by: Stan |
last post by:
I am using MS Office Access 2003 (11.5614). My basic question is can
I run a query of a query datasheet. I want to use more that one
criteria and can not get that query to work. I thought I...
|
by: jsacrey |
last post by:
Hey everybody, got a secnario for ya that I need a bit of help with.
Access 97 using linked tables from an SQL Server 2000 machine.
I've created a simple query using two tables joined by one...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |