Time Scheduling Conflict Check

[hrdrckr]

There was a post that Allen Browne answered about a year and a half ago
about Date Scheduling conflict checks. I'm looking for the exact same
thing but with times. The time format I use in my database is the
medium time format. I tried to incorporate it into the sample that
Allen gave, but, I think I am having a problem with the conJetDate
format for time.

The following is a copy of the thread from a year and a half ago. If
someone can please help me get this working with times instead of dates
I would greatly appreciate it. Thanks!

5 From: Allen Browne - view profile
Date: Wed, Jan 19 2005 8:00 pm
Email: "Allen Browne" <[email protected]>
Groups: microsoft.public.access.tablesdbdesign
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author


I think you're working with the code from the article that compares
every
record against every other record to find the clashes. For your case,
you
only need to check if there is any record that matches the one being
entered.

Something like this (untested aircode):


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


If IsNull(Me.[Start]) Or IsNull(Me.[End Date]) Or _
IsNull(Me.[TechID]) Or IsNull(Me.EventID) Then
Cancel = True
MsgBox = "Both dates, TechID, and EventID required."
Else
strWhere = "([Start] < " & Format(Me.[End Date], conJetDate) &
_
") AND (" & Format(Me.Start, conJetDate) & " < [End Date]) AND
(TechID = " & _
Me.TechID & ") AND (EventID = " & Me.EventID & ")"
If Not Me.NewRecord Then 'Doesn't clash with itself.
strWhere = strWhere & " AND ([ScheduleID] <> " &
Me.[ScheduleID]
& ")"
End If


varResult = DLookup("[ScheduleID]", "Schedule", strWhere)
If Not IsNull(varResult) Then
strMsg = "Clash with ScheduleID " & varResult & vbCrLf & _
"Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2) <> vbYes Then
Cancel = True
End If
End If
End If
End Sub


That assumes that the primarykey is ScheduleID, and fields TechID,
EventID,
and ScheduleID are both Number type fields. Needs extra quotes if they
are
Text fields.


If that doesn't work, add:
Debug.Print strWhere
Then mock up a query, switch it to SQL View (View menu, in query
design),
and compare the WHERE clause with what is printed in the Debug window
(Ctrl+G) when the code runs.


General help on DLookup():
http://members.iinet.net.au/~allenbrowne/casu-07.html


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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







- Hide quoted text -
- Show quoted text -

I seem to be stuck.
I don't understand how I am supposed to do this. My query loos like this

NoClash: ([Schedule ID_1].[Start]>=[Schedule ID].[End Date]) Or ([Schedule
ID_1].[End Date]<=[Schedule ID].[Start date]) Or ([Schedule ID].[Tech
ID]<>[Schedule ID_1].[Tech ID]) Or ([Schedule ID].[EventID]=[Schedule
ID_1].[EventID])

currently in BeforeUpdate I have
=[Start date]<=Schedule![Task Start Date] And [End Date]>=Schedule![Task
End
Date]

How do I include both of these functions to run before update.
Thanks again,

:

Use DLookup() in the BeforeUpdate event of the form to see if a clashing
record exists.

The 3rd argument (Criteria) will be involved, but it will work.

Reply Rate this post: Text for clearing space

6 From: Wakequest - view profile
Date: Mon, Jan 24 2005 2:39 pm
Email: Wakequest <[email protected]>
Groups: microsoft.public.access.tablesdbdesign
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author


I got it.
Thanks for you help. Here is what I finally came up with.
My table was set up like this:

TechScehdule


TechScehduleID Number Primary Key
ScheduleID Number
Startdate Date
EndDate Date


The Code looks like this:


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


If IsNull(Me.Startdate) Or IsNull(Me.EndDate) Or _
IsNull(Me.TechID) Or IsNull(Me.ScheduleID) Then
Cancel = True
MsgBox ("Both dates, Tech ID, and Tech Schedule ID required.")
Else
strWhere = "(Startdate < " & Format(Me.EndDate, conJetDate) &
") " & _
"AND (" & Format(Me.Startdate, conJetDate) & " < EndDate) "
& _
"AND (TechID = " & Me.TechID & ") " & _
"AND (TechscheduleID <>" & Me.TechScheduleID & ") " & _
I
made a change here from = to <>
"AND (ScheduleID <> " & Me.ScheduleID & ")"
If Not Me.NewRecord Then 'Does not clash with self.
varResult = DLookup([TechScheduleID], "TechSchedule",
strWhere)
and A change here from " " to [ ]
If Not IsNull(varResult) Then
strMsg = "Clash with Tech Schedule ID." & vbCrLf &
_
"Continue anyway?"
If MsgBox(strMsg, vbQuestion + vbYesNo +
vbDefaultButton2)
<> vbYes Then
Cancel = True
End If
End If
End If
End If
End Sub

 

[hrdrckr]

Does anybody have any ideas on my question? Thanks!

There was a post that Allen Browne answered about a year and a half ago
about Date Scheduling conflict checks. I'm looking for the exact same
thing but with times. The time format I use in my database is the
medium time format. I tried to incorporate it into the sample that
Allen gave, but, I think I am having a problem with the conJetDate
format for time.

The following is a copy of the thread from a year and a half ago. If
someone can please help me get this working with times instead of dates
I would greatly appreciate it. Thanks!

5 From: Allen Browne - view profile
Date: Wed, Jan 19 2005 8:00 pm
Email: "Allen Browne" <[email protected]>
Groups: microsoft.public.access.tablesdbdesign
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author


I think you're working with the code from the article that compares
every
record against every other record to find the clashes. For your case,
you
only need to check if there is any record that matches the one being
entered.

Something like this (untested aircode):


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


If IsNull(Me.[Start]) Or IsNull(Me.[End Date]) Or _
IsNull(Me.[TechID]) Or IsNull(Me.EventID) Then
Cancel = True
MsgBox = "Both dates, TechID, and EventID required."
Else
strWhere = "([Start] < " & Format(Me.[End Date], conJetDate) &
_
") AND (" & Format(Me.Start, conJetDate) & " < [End Date]) AND
(TechID = " & _
Me.TechID & ") AND (EventID = " & Me.EventID & ")"
If Not Me.NewRecord Then 'Doesn't clash with itself.
strWhere = strWhere & " AND ([ScheduleID] <> " &
Me.[ScheduleID]
& ")"
End If


varResult = DLookup("[ScheduleID]", "Schedule", strWhere)
If Not IsNull(varResult) Then
strMsg = "Clash with ScheduleID " & varResult & vbCrLf & _
"Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2) <> vbYes Then
Cancel = True
End If
End If
End If
End Sub


That assumes that the primarykey is ScheduleID, and fields TechID,
EventID,
and ScheduleID are both Number type fields. Needs extra quotes if they
are
Text fields.


If that doesn't work, add:
Debug.Print strWhere
Then mock up a query, switch it to SQL View (View menu, in query
design),
and compare the WHERE clause with what is printed in the Debug window
(Ctrl+G) when the code runs.


General help on DLookup():
http://members.iinet.net.au/~allenbrowne/casu-07.html


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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







- Hide quoted text -
- Show quoted text -

I seem to be stuck.
I don't understand how I am supposed to do this. My query loos like this

NoClash: ([Schedule ID_1].[Start]>=[Schedule ID].[End Date]) Or ([Schedule
ID_1].[End Date]<=[Schedule ID].[Start date]) Or ([Schedule ID].[Tech
ID]<>[Schedule ID_1].[Tech ID]) Or ([Schedule ID].[EventID]=[Schedule
ID_1].[EventID])

currently in BeforeUpdate I have
=[Start date]<=Schedule![Task Start Date] And [End Date]>=Schedule![Task
End
Date]

How do I include both of these functions to run before update.
Thanks again,

:

Use DLookup() in the BeforeUpdate event of the form to see if a clashing
record exists.

The 3rd argument (Criteria) will be involved, but it will work.

Thank you for you reply.
Your website was very helpful in giving me the correct code to use.
I have one more question though. Is it possible for the conflict to be
determined without running a query? So at the point of entering the
conflicting schedule the user will receive a warning that the employee
is
already busy during the time requested. This way instead of searching
for
conflicts they are restricted form being entered. If not I can create
a
workaround however it would be best if I could avoid the conflicts all
together.

"Allen Browne" wrote:

Perform the validation in the BeforeUpdate event of the form where
employees
are assigned to the project, i.e. in Form_BeforeUpdate.

Two assignments overlap if:
- A begins before B ends, AND
- B begins before A ends.

The assignment start date and end date should both be between the
project
start date and end date.

Use those facts to create a SQL statement, and OpenRecordset().

I am building a database for tracking employees assigned to projects
and
project tasks. I have two primary tables set up with Start Date and
End
Date, One for the project itself and one for the Employee asssigned
to
the
task. Mulipul Employees can be assigned to each task and project
which
is
perfect. However I need to limit the fields so an employee does not
get
double booked on two seperated projects during the same date range.
Additional I would like for the date range for the assignment to not
go
outside of the project date range.
Please Help!

Reply Rate this post: Text for clearing space

6 From: Wakequest - view profile
Date: Mon, Jan 24 2005 2:39 pm
Email: Wakequest <[email protected]>
Groups: microsoft.public.access.tablesdbdesign
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author


I got it.
Thanks for you help. Here is what I finally came up with.
My table was set up like this:

TechScehdule


TechScehduleID Number Primary Key
ScheduleID Number
Startdate Date
EndDate Date


The Code looks like this:


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


If IsNull(Me.Startdate) Or IsNull(Me.EndDate) Or _
IsNull(Me.TechID) Or IsNull(Me.ScheduleID) Then
Cancel = True
MsgBox ("Both dates, Tech ID, and Tech Schedule ID required.")
Else
strWhere = "(Startdate < " & Format(Me.EndDate, conJetDate) &
") " & _
"AND (" & Format(Me.Startdate, conJetDate) & " < EndDate) "
& _
"AND (TechID = " & Me.TechID & ") " & _
"AND (TechscheduleID <>" & Me.TechScheduleID & ") " & _
I
made a change here from = to <>
"AND (ScheduleID <> " & Me.ScheduleID & ")"
If Not Me.NewRecord Then 'Does not clash with self.
varResult = DLookup([TechScheduleID], "TechSchedule",
strWhere)
and A change here from " " to [ ]
If Not IsNull(varResult) Then
strMsg = "Clash with Tech Schedule ID." & vbCrLf &
_
"Continue anyway?"
If MsgBox(strMsg, vbQuestion + vbYesNo +
vbDefaultButton2)
<> vbYes Then
Cancel = True
End If
End If
End If
End If
End Sub

 

[Duane Hookom]

Do you have some sample records with table and field names?

--
Duane Hookom
MS Access MVP

There was a post that Allen Browne answered about a year and a half ago
about Date Scheduling conflict checks. I'm looking for the exact same
thing but with times. The time format I use in my database is the
medium time format. I tried to incorporate it into the sample that
Allen gave, but, I think I am having a problem with the conJetDate
format for time.

The following is a copy of the thread from a year and a half ago. If
someone can please help me get this working with times instead of dates
I would greatly appreciate it. Thanks!

5 From: Allen Browne - view profile
Date: Wed, Jan 19 2005 8:00 pm
Email: "Allen Browne" <[email protected]>
Groups: microsoft.public.access.tablesdbdesign
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author


I think you're working with the code from the article that compares
every
record against every other record to find the clashes. For your case,
you
only need to check if there is any record that matches the one being
entered.

Something like this (untested aircode):


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


If IsNull(Me.[Start]) Or IsNull(Me.[End Date]) Or _
IsNull(Me.[TechID]) Or IsNull(Me.EventID) Then
Cancel = True
MsgBox = "Both dates, TechID, and EventID required."
Else
strWhere = "([Start] < " & Format(Me.[End Date], conJetDate) &
_
") AND (" & Format(Me.Start, conJetDate) & " < [End Date]) AND
(TechID = " & _
Me.TechID & ") AND (EventID = " & Me.EventID & ")"
If Not Me.NewRecord Then 'Doesn't clash with itself.
strWhere = strWhere & " AND ([ScheduleID] <> " &
Me.[ScheduleID]
& ")"
End If


varResult = DLookup("[ScheduleID]", "Schedule", strWhere)
If Not IsNull(varResult) Then
strMsg = "Clash with ScheduleID " & varResult & vbCrLf & _
"Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2) <> vbYes Then
Cancel = True
End If
End If
End If
End Sub


That assumes that the primarykey is ScheduleID, and fields TechID,
EventID,
and ScheduleID are both Number type fields. Needs extra quotes if they
are
Text fields.


If that doesn't work, add:
Debug.Print strWhere
Then mock up a query, switch it to SQL View (View menu, in query
design),
and compare the WHERE clause with what is printed in the Debug window
(Ctrl+G) when the code runs.


General help on DLookup():
http://members.iinet.net.au/~allenbrowne/casu-07.html


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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







- Hide quoted text -
- Show quoted text -

I seem to be stuck.
I don't understand how I am supposed to do this. My query loos like this

NoClash: ([Schedule ID_1].[Start]>=[Schedule ID].[End Date]) Or
([Schedule
ID_1].[End Date]<=[Schedule ID].[Start date]) Or ([Schedule ID].[Tech
ID]<>[Schedule ID_1].[Tech ID]) Or ([Schedule ID].[EventID]=[Schedule
ID_1].[EventID])

currently in BeforeUpdate I have
=[Start date]<=Schedule![Task Start Date] And [End Date]>=Schedule![Task
End
Date]

How do I include both of these functions to run before update.
Thanks again,

:

Use DLookup() in the BeforeUpdate event of the form to see if a clashing
record exists.

The 3rd argument (Criteria) will be involved, but it will work.

Thank you for you reply.
Your website was very helpful in giving me the correct code to use.
I have one more question though. Is it possible for the conflict to
be
determined without running a query? So at the point of entering the
conflicting schedule the user will receive a warning that the employee
is
already busy during the time requested. This way instead of searching
for
conflicts they are restricted form being entered. If not I can create
a
workaround however it would be best if I could avoid the conflicts all
together.

"Allen Browne" wrote:

Perform the validation in the BeforeUpdate event of the form where
employees
are assigned to the project, i.e. in Form_BeforeUpdate.

Two assignments overlap if:
- A begins before B ends, AND
- B begins before A ends.

The assignment start date and end date should both be between the
project
start date and end date.

Use those facts to create a SQL statement, and OpenRecordset().

I am building a database for tracking employees assigned to projects
and
project tasks. I have two primary tables set up with Start Date
and
End
Date, One for the project itself and one for the Employee
asssigned
to
the
task. Mulipul Employees can be assigned to each task and project
which
is
perfect. However I need to limit the fields so an employee does
not
get
double booked on two seperated projects during the same date range.
Additional I would like for the date range for the assignment to
not
go
outside of the project date range.
Please Help!

Reply Rate this post: Text for clearing space

6 From: Wakequest - view profile
Date: Mon, Jan 24 2005 2:39 pm
Email: Wakequest <[email protected]>
Groups: microsoft.public.access.tablesdbdesign
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author


I got it.
Thanks for you help. Here is what I finally came up with.
My table was set up like this:

TechScehdule


TechScehduleID Number Primary Key
ScheduleID Number
Startdate Date
EndDate Date


The Code looks like this:


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


If IsNull(Me.Startdate) Or IsNull(Me.EndDate) Or _
IsNull(Me.TechID) Or IsNull(Me.ScheduleID) Then
Cancel = True
MsgBox ("Both dates, Tech ID, and Tech Schedule ID required.")
Else
strWhere = "(Startdate < " & Format(Me.EndDate, conJetDate) &
") " & _
"AND (" & Format(Me.Startdate, conJetDate) & " < EndDate) "
& _
"AND (TechID = " & Me.TechID & ") " & _
"AND (TechscheduleID <>" & Me.TechScheduleID & ") " & _
I
made a change here from = to <>
"AND (ScheduleID <> " & Me.ScheduleID & ")"
If Not Me.NewRecord Then 'Does not clash with self.
varResult = DLookup([TechScheduleID], "TechSchedule",
strWhere)
and A change here from " " to [ ]
If Not IsNull(varResult) Then
strMsg = "Clash with Tech Schedule ID." & vbCrLf &
_
"Continue anyway?"
If MsgBox(strMsg, vbQuestion + vbYesNo +
vbDefaultButton2)
<> vbYes Then
Cancel = True
End If
End If
End If
End If
End Sub

 

[Tim Ferguson]

The time format I use in my database is the
medium time format. I tried to incorporate it into the sample that
Allen gave, but, I think I am having a problem with the conJetDate
format for time.

You don't actually have a question anywhere in the post as far as I can
see.

Still, the phrase "tried to incorporate [the medium time format] into the
sample" is probably the clue. Jet uses a very limited number of formats for
date-time values... one is USA and the other is ISO and the rest are not
worth talking about. Normal d/m/y formats are not going to be recognised
correctly anywhere in the world.

Use the Const conJetDate = "\#mm\/dd\/yyyy\#" format exactly as it is,
regardless of your local settings.

Hope that helps


Tim F