date overlap

[Jon]

I want to build a database to track an employee work day hours. I have a
group of employees and each employee should not excess 12 Hours per day. What
I want to do is making a table for entering employee ID ,Date & working Hours
.. when I want to input employee record, access should check if this employee
is with Hours limit(12 HOURS) if yes, access precedes with record, if not
access show me the capability of hours still and give me alternative of other
employees with the same group who can work the number of hours I entered. Is
that possible in access. Please help???

Thanks

 

[kc-mass]

This assumes that you have a record of hours already scheduled by employee.
The below query would give you every one that has time available for the
assignment.
You could make this the source for a list or combo box and select from those
eligible.


SELECT EmplID, HrsScheduled, WorkDate
FROM tblScheduled
WHERE (((HrsScheduled)<=12-Forms!MyForm!txtAssignmentHrs)
AND ((WorkDate)=Forms!MyForm!txtDateOfAssignment));

Regards

Kevin

 

[Jon]

Thank you kc-mass for your reply

but I need to prevent Scheduled table from entering data if this employee
excess the 12 hours by showing a msg and cancel the event. Please advice??

 

[kc-mass]

The only employees that you will be able to pick are those with sufficient
time left in their 12 hour schedule. That is if you have an assignment for
3.5 hours then the only employees this query will show you are those that
have 8.5 hours or less currently scheduled on that day. Employees with 8.6
or more hours already scheduled on that day won't show and you then cannot
pick them and they cannot get the assignment.

Regards

Kevin

 

[Allen Browne]

Use the BeforeUpdate event procedure of the form (not control.) Use DSum()
to get the number of hours from *other* records, and add this one. (That
approach will work for new records and edited records.)

The event procedure will be something like the example below. Note that:
a) I've assumed the Employee ID field is named EmpID. Add square brackets
around the name if it contains spaces, e.g. [Emp ID]
b) I've assumed the Date field is named WorkDate. DATE is a reserved name in
Access and can cause you grief.

c) I've assumed the Hours field is a Number type (not Date/Time.)

d) I've assumed the primary key field is named ID. We use that to exclude
the current record from the DSum().

e) Don't change the JET Date format constant (regardless of your regional
settings.)

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim dblHours as Long
Dim strWhere As String
Dim strMsg As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"
Const dblcMaxHours As Double = 12

If IsNull(Me.EmpID) Then
Cancel = True
strMsg = strMsg & "Employee required." & vbCrLf
End If
If IsNull(Me.WorkDate) Then
Cancel = True
strMsg = strMsg & "Date required." & vbCrLf
End If
If IsNull(Me.Hours) Then
Cancel = True
strMsg = strMsg & "Hours required." & vbCrLf
End If
If Cancel Then
strMsg = strMsg & vbCrLf & "Complete the data, or press Esc to
undo."
MsgBox strMsg, vbExclamation, "Incomplete"
Else
strWhere = "(EmpID = " & Me.EmpID & ") AND (WorkDate = " & _
Format(Me.WorkDate, strcJetDate) & "(ID <> " & Me.ID & ")"
dblHours = Nz(DSum("Hours", "Table1", strWhere),0) + Me.Hours
If dblHours > dblcMaxHours Then
strMsg = "This employee now has " & dblHours & _
" hour(s) on this date." & vbCrLf & "Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Overload") <> vbYes
Then
Cancel = True
'Me.Undo
End If
End If
End If
End Sub

 

[Jon]

Thank you all
Mr. Allen, I have the following error
Error3075
Missing ),], or item in query expression ‘(EmpID=234)AND (WorkDate =
#07/07/2009#(ID<>3)’.
And the following line is highlighted
dblHours = Nz(DSum("Hours", "WorkSchu", strWhere), 0) + Me.Hours

please advice??

Use the BeforeUpdate event procedure of the form (not control.) Use DSum()
to get the number of hours from *other* records, and add this one. (That
approach will work for new records and edited records.)

The event procedure will be something like the example below. Note that:
a) I've assumed the Employee ID field is named EmpID. Add square brackets
around the name if it contains spaces, e.g. [Emp ID]
b) I've assumed the Date field is named WorkDate. DATE is a reserved name in
Access and can cause you grief.

c) I've assumed the Hours field is a Number type (not Date/Time.)

d) I've assumed the primary key field is named ID. We use that to exclude
the current record from the DSum().

e) Don't change the JET Date format constant (regardless of your regional
settings.)

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim dblHours as Long
Dim strWhere As String
Dim strMsg As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"
Const dblcMaxHours As Double = 12

If IsNull(Me.EmpID) Then
Cancel = True
strMsg = strMsg & "Employee required." & vbCrLf
End If
If IsNull(Me.WorkDate) Then
Cancel = True
strMsg = strMsg & "Date required." & vbCrLf
End If
If IsNull(Me.Hours) Then
Cancel = True
strMsg = strMsg & "Hours required." & vbCrLf
End If
If Cancel Then
strMsg = strMsg & vbCrLf & "Complete the data, or press Esc to
undo."
MsgBox strMsg, vbExclamation, "Incomplete"
Else
strWhere = "(EmpID = " & Me.EmpID & ") AND (WorkDate = " & _
Format(Me.WorkDate, strcJetDate) & "(ID <> " & Me.ID & ")"
dblHours = Nz(DSum("Hours", "Table1", strWhere),0) + Me.Hours
If dblHours > dblcMaxHours Then
strMsg = "This employee now has " & dblHours & _
" hour(s) on this date." & vbCrLf & "Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Overload") <> vbYes
Then
Cancel = True
'Me.Undo
End If
End If
End If
End Sub

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

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

I want to build a database to track an employee work day hours. I have
a group of employees and each employee should not excess 12 Hours
per day. What I want to do is making a table for entering employee ID,
Date & working Hours . when I want to input employee record, access
should check if this employee is with Hours limit(12 HOURS) if yes,
access precedes with record, if not access show me the capability of
hours still and give me alternative of other employees with the same
group who can work the number of hours I entered. Is that possible in
access. Please help???

 

[Douglas J. Steele]

Slight typo: there's a missing keyword ) AND in there. Change the code to

strWhere = "(EmpID = " & Me.EmpID & ") " & _
" AND (WorkDate = " & Format(Me.WorkDate, strcJetDate) & ") " & _
" AND (ID <> " & Me.ID & ")"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you all
Mr. Allen, I have the following error
Error3075
Missing ),], or item in query expression '(EmpID=234)AND (WorkDate =
#07/07/2009#(ID<>3)'.
And the following line is highlighted
dblHours = Nz(DSum("Hours", "WorkSchu", strWhere), 0) + Me.Hours

please advice??

Use the BeforeUpdate event procedure of the form (not control.) Use
DSum()
to get the number of hours from *other* records, and add this one. (That
approach will work for new records and edited records.)

The event procedure will be something like the example below. Note that:
a) I've assumed the Employee ID field is named EmpID. Add square brackets
around the name if it contains spaces, e.g. [Emp ID]
b) I've assumed the Date field is named WorkDate. DATE is a reserved name
in
Access and can cause you grief.

c) I've assumed the Hours field is a Number type (not Date/Time.)

d) I've assumed the primary key field is named ID. We use that to exclude
the current record from the DSum().

e) Don't change the JET Date format constant (regardless of your regional
settings.)

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim dblHours as Long
Dim strWhere As String
Dim strMsg As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"
Const dblcMaxHours As Double = 12

If IsNull(Me.EmpID) Then
Cancel = True
strMsg = strMsg & "Employee required." & vbCrLf
End If
If IsNull(Me.WorkDate) Then
Cancel = True
strMsg = strMsg & "Date required." & vbCrLf
End If
If IsNull(Me.Hours) Then
Cancel = True
strMsg = strMsg & "Hours required." & vbCrLf
End If
If Cancel Then
strMsg = strMsg & vbCrLf & "Complete the data, or press Esc to
undo."
MsgBox strMsg, vbExclamation, "Incomplete"
Else
strWhere = "(EmpID = " & Me.EmpID & ") AND (WorkDate = " & _
Format(Me.WorkDate, strcJetDate) & "(ID <> " & Me.ID & ")"
dblHours = Nz(DSum("Hours", "Table1", strWhere),0) + Me.Hours
If dblHours > dblcMaxHours Then
strMsg = "This employee now has " & dblHours & _
" hour(s) on this date." & vbCrLf & "Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Overload") <>
vbYes
Then
Cancel = True
'Me.Undo
End If
End If
End If
End Sub

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

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

I want to build a database to track an employee work day hours. I have
a group of employees and each employee should not excess 12 Hours
per day. What I want to do is making a table for entering employee ID,
Date & working Hours . when I want to input employee record, access
should check if this employee is with Hours limit(12 HOURS) if yes,
access precedes with record, if not access show me the capability of
hours still and give me alternative of other employees with the same
group who can work the number of hours I entered. Is that possible in
access. Please help???

 

[Jon]

Thank you all,

One more question
Is there any way to show a list box with alternative employees + available
hours who can be assigned for those hours if the current employee hours is
full. I have a table for employee info with more than one employee can be
assigned. How to do that please??

Slight typo: there's a missing keyword ) AND in there. Change the code to

strWhere = "(EmpID = " & Me.EmpID & ") " & _
" AND (WorkDate = " & Format(Me.WorkDate, strcJetDate) & ") " & _
" AND (ID <> " & Me.ID & ")"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you all
Mr. Allen, I have the following error
Error3075
Missing ),], or item in query expression '(EmpID=234)AND (WorkDate =
#07/07/2009#(ID<>3)'.
And the following line is highlighted
dblHours = Nz(DSum("Hours", "WorkSchu", strWhere), 0) + Me.Hours

please advice??

Use the BeforeUpdate event procedure of the form (not control.) Use
DSum()
to get the number of hours from *other* records, and add this one. (That
approach will work for new records and edited records.)

The event procedure will be something like the example below. Note that:
a) I've assumed the Employee ID field is named EmpID. Add square brackets
around the name if it contains spaces, e.g. [Emp ID]
b) I've assumed the Date field is named WorkDate. DATE is a reserved name
in
Access and can cause you grief.

c) I've assumed the Hours field is a Number type (not Date/Time.)

d) I've assumed the primary key field is named ID. We use that to exclude
the current record from the DSum().

e) Don't change the JET Date format constant (regardless of your regional
settings.)

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim dblHours as Long
Dim strWhere As String
Dim strMsg As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"
Const dblcMaxHours As Double = 12

If IsNull(Me.EmpID) Then
Cancel = True
strMsg = strMsg & "Employee required." & vbCrLf
End If
If IsNull(Me.WorkDate) Then
Cancel = True
strMsg = strMsg & "Date required." & vbCrLf
End If
If IsNull(Me.Hours) Then
Cancel = True
strMsg = strMsg & "Hours required." & vbCrLf
End If
If Cancel Then
strMsg = strMsg & vbCrLf & "Complete the data, or press Esc to
undo."
MsgBox strMsg, vbExclamation, "Incomplete"
Else
strWhere = "(EmpID = " & Me.EmpID & ") AND (WorkDate = " & _
Format(Me.WorkDate, strcJetDate) & "(ID <> " & Me.ID & ")"
dblHours = Nz(DSum("Hours", "Table1", strWhere),0) + Me.Hours
If dblHours > dblcMaxHours Then
strMsg = "This employee now has " & dblHours & _
" hour(s) on this date." & vbCrLf & "Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Overload") <>
vbYes
Then
Cancel = True
'Me.Undo
End If
End If
End If
End Sub

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

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

I want to build a database to track an employee work day hours. I have
a group of employees and each employee should not excess 12 Hours
per day. What I want to do is making a table for entering employee ID,
Date & working Hours . when I want to input employee record, access
should check if this employee is with Hours limit(12 HOURS) if yes,
access precedes with record, if not access show me the capability of
hours still and give me alternative of other employees with the same
group who can work the number of hours I entered. Is that possible in
access. Please help???