Appointment Dates

[Stephen]

I have a table as detailed below. I am wanting the user to to enter a date,
time and appoinment duration for a Sales Rep. Then once they have done this
I want the system to look up and make sure that this sales rep doesn't have
an appoinment already in this time period.

Table Details:

Table Name: Quotation

Quote_ID Appt_Date Appt_Time Appt_Duration Sales_Rep

(Mins)
123 15/04/04 09:30 90
Chris
124 15/04/04 08:30 45
Steve
125 15/04/04 10:00 30
Chris
126 15/04/04 08:30 45
Dave
127 15/04/04 09:00 45
Steve

Then I would like to say enter the following appt, which would not be
allowed as it is between the appt_time start and end times. This is done
with the DateAdd feature of Appt_Time and Appt_Duration in mins.

If anyone can help with this it would be appreciated.

TIA
Stephen

 

[Allen Browne]

Two appointments clash if:
A starts before B ends, AND
B starts before A ends.

Use DateAdd() to get the ending time:
DateAdd("n", [Appt_Duration], [Appt_Time])

Then use a DLookup() in the BeforeUpdate event of the *form* (since several
fields are involved) to see if there is a clash. Assuming all values are
present, it would look something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Const conJetDate = "\#mm\/dd\/yyyy\#"

strWhere = "(" & Format(Me.[Appt_Time], conJetDate) & _
" < DateAdd(""n"", [Appt_Duration], [Appt_Time])) AND ([Appt_Time] < " & _
Format(DateAdd("n", Me.[Appt_Duration], Me.[Appt_Time]), conJetDate) & _
") AND ([Sales_Rep] = " & Me.[Sales_Rep] & ") AND ([Quote_ID] <> " &
Me.[QuoteID] & ")"

varResult = DLookup("Quote_ID", "Quotation", strWhere)
If Not IsNull(varResult) Then
If MsgBox("Clash with Quote " & varResult & vbCrLf & "Continue anyway?",
vbYesNo+vbDefaultButton2) <> vbYes Then
Cancel = True
End If
End If
End Sub

 

[Stephen]

Thanks for the code Allen.

I tried this code but everytime I insert the code in I get an error message
saying that Compile Error, Expected: End of Statement on the strWhere
expression.

If you or anyone could help with this it would be greatly appreciated.

TIA
Stephen

Two appointments clash if:
A starts before B ends, AND
B starts before A ends.

Use DateAdd() to get the ending time:
DateAdd("n", [Appt_Duration], [Appt_Time])

Then use a DLookup() in the BeforeUpdate event of the *form* (since several
fields are involved) to see if there is a clash. Assuming all values are
present, it would look something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Const conJetDate = "\#mm\/dd\/yyyy\#"

strWhere = "(" & Format(Me.[Appt_Time], conJetDate) & _
" < DateAdd(""n"", [Appt_Duration], [Appt_Time])) AND ([Appt_Time] < " & _
Format(DateAdd("n", Me.[Appt_Duration], Me.[Appt_Time]), conJetDate) & _
") AND ([Sales_Rep] = " & Me.[Sales_Rep] & ") AND ([Quote_ID] <> " &
Me.[QuoteID] & ")"

varResult = DLookup("Quote_ID", "Quotation", strWhere)
If Not IsNull(varResult) Then
If MsgBox("Clash with Quote " & varResult & vbCrLf & "Continue anyway?",
vbYesNo+vbDefaultButton2) <> vbYes Then
Cancel = True
End If
End If
End Sub
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table as detailed below. I am wanting the user to to enter a date,
time and appoinment duration for a Sales Rep. Then once they have done this
I want the system to look up and make sure that this sales rep doesn't have
an appoinment already in this time period.

Table Details:

Table Name: Quotation

Quote_ID Appt_Date Appt_Time Appt_Duration Sales_Rep

(Mins)
123 15/04/04 09:30 90
Chris
124 15/04/04 08:30 45
Steve
125 15/04/04 10:00 30
Chris
126 15/04/04 08:30 45
Dave
127 15/04/04 09:00 45
Steve

Then I would like to say enter the following appt, which would not be
allowed as it is between the appt_time start and end times. This is done
with the DateAdd feature of Appt_Time and Appt_Duration in mins.

If anyone can help with this it would be appreciated.

TIA
Stephen