Conflicting time for work schedule

**Rabbit** · Jul 4 '13, 03:58 AM

You need to show us the code you're using. And tell us what you mean by not working.

**Tamakm** · Jul 6 '13, 01:46 PM

I am not a programmer.

Like access and create simple databases.

I have this code in my query which I found on line.
I have added this code in my query.
I created a report from this query and added the report in form command button to give alert that there is a conflict.

Soon I add location each and every approval that message comes up because all employees have signed up for overlapping overtime.

So I think this can only work when you try to add new record. But My table already has all the record.

The only thing I am doing is adding the location in the form that is based on this same table which has all the records including start and end time.

I would appreciate your help

Code:

NoOTClash: 
([EqualizationToApproveAdvOT_1].[OT-StartTime]
      >=[EqualizationToApproveAdvOT].[OT-EndTime]) 
   Or ([EqualizationToApproveAdvOT_1].[OT-EndTime]
      <=[EqualizationToApproveAdvOT].[OT-StartTime])
   Or ([EqualizationToApproveAdvOT].[OfficersName]
      <>[EqualizationToApproveAdvOT_1].[OfficersName])
   Or ([EqualizationToApproveAdvOT].[OT_Date]
      <>[EqualizationToApproveAdvOT_1].[OT_Date]) 
   Or ([EqualizationToApproveAdvOT].[Overtime_lineNumber]
      =[EqualizationToApproveAdvOT_1].Overtime_lineNumber])

**zmbd** · Jul 6 '13, 07:41 PM

Please provide the entire code...

Post#1>...I have created a form to approve this overtime.

Post#3>...I created a report from this query and added the report in form command button to give alert that there is a conflict.

I suspect that you mean form and not report in your second post, yes? The two terms are NOT interchangable.

We'll need the full code behind the control, not just the little snip-it:
Open your form in design mode.
Right click on the command button.
In the pop-up menu, at the bottom, select properties.
Select the "Events" tab.
You will now see a listing of event triggers with names on the left side and and, if there is a code attached...
In the "on click" you will see either [Event Procedure] or [Embedded Macro], if you do not see this please post back.
In either case, click on the button that shows [...] in it:

In the case of the [Event Procedure], the VBA editor will open to the code related to the control. Please select the entire text, including the "Sub (name or your control)_click( ) thru and inculding "End Sub"
In the case of [Embedded Macro], the Macro editor will open. If you are using either Access V2007 or V2010, then you can <ctrlA>, <ctrlC> otherwise you will have to hand type the macro into a post.

Now, in the reply box at the bottom here, FIRST click the [CODE/] button, this will insert "[code]..[/code]" between these two tags, you will either <ctrlV> to paste the copied text into the post, or where you will have to hand enter the macro code.
Now you may submit the post.
PLEASE, do NOT forget to enter your code between the two "[code]..[/code]" tags.

**Tamakm** · Jul 7 '13, 03:47 AM

Apologize for posting code improperly. Actually, I have the query with the code listed previously With two copies of the table in that query. Than I created a report based on this query. and than created a form and added command button with the code below. But since I am not entering Date, StartTime, EndTime (Because this is already listed in the table. I just add a location for employee as I am approving the overtime. This code is not working. Thank You.

Code:

 Public Function Check()
   Dim ApprovedOTLocation
   DoCmd.OpenReport "OTTimeConflictToApproveAdvOT", _
      acViewPreview
   Score = Reports![OTTimeConflictToApproveAdvOT].HasData
   DoCmd.Close acReport, "OTTimeConflictToApproveAdvOT"
   If Score = -1 Then
      MsgBox "There is a clash of Overtime Hours, Please try again."
   End If
End Function

**Rabbit** · Jul 7 '13, 05:40 AM

We would need to see the full SQL, not just the expression that was written.

**zmbd** · Jul 7 '13, 06:02 AM

No apology required. Happens quite frequently. :)

A Function call is not normally what I would expect and would not norrmally be the code directly triggered via the command button. Please re-read my post and post the code for the command button too... if this is the VBA code, then there must be an [Embedded Macro] indicator in the control event code, we'll need to know that information.

If I am reading your post correctly, you have a command button on your report that calls up the form?

IF this is correct, you will have additional issues. I know that in a twitterpated moment, Microsoft tried adding interactivity to reports. This is fine until the user tries to modify the underlying record-set and it either crashes, causes an error, or just does nothing.

It is best practice to keep in mind the following:

Forms are for interacting with the user.
Forms are for calling reports once the user has finished and needs a pretty picture for the customer (be that the boss or a client, doesn't matter)
Reports are for telling the user what happened or for creating that receipt for the client.

**Tamakm** · Aug 9 '13, 05:34 PM

CompletePicture of database

Originally posted by Rabbit

We would need to see the full SQL, not just the expression that was written.

SQL below is in Access Query and Report is based on query which is run by function in the form command button.

Code:

NoOTClash:([EqualizationToApproveAdvOT_1].[OT-StartTime]>=[EqualizationToApproveAdvOT].[OT-EndTime]) 

Or ([EqualizationToApproveAdvOT_1].[OT-EndTime]<=[EqualizationToApproveAdvOT].[OT-StartTime]) 

Or ([EqualizationToApproveAdvOT].[OfficersName]<>[EqualizationToApproveAdvOT_1].[OfficersName]) 

Or ([EqualizationToApproveAdvOT].[OT_Date]<>[EqualizationToApproveAdvOT_1].[OT_Date]) 

Or ([EqualizationToApproveAdvOT].[ApprovedOTLocation]=" ")

User only adds location as approved.
once a location is assigned,
when user comes accross same name with same date with overlapping start and End Time.
Soon user adds location program should alert for overlapping of the shift that is approved previously.

I got this code from one of the site.

'Function below is in Form Command Button that is for Report I have created with Above query.
The report has all the information except for Location which is the only thing user is adding as approved.

Code:

Public Function Check()
Dim ApprovedOTLocation
DoCmd.OpenReport "OTTimeConflictToApproveAdvOT", acViewPreview
Score = Reports![OTTimeConflictToApproveAdvOT].HasData
DoCmd.Close acReport, "OTTimeConflictToApproveAdvOT"
If Score = -1 Then
MsgBox "There is a clash of Overtime Hours, Please try again."
End If
End Function

Attached Files

**Rabbit** · Aug 9 '13, 06:45 PM

That's just the expression, where's the whole SQL command?

**Tamakm** · Aug 10 '13, 02:18 AM

Originally posted by Rabbit

That's just the expression, where's the whole SQL command?

Top box in my reply is with SQL statement in my Query
and I am using that query in the report.
Not using other SQL cmmand for this. I have attached the table, Query and form.

**Rabbit** · Aug 10 '13, 03:24 AM

No, that's not the full SQL, that's just an expression.

**Tamakm** · Aug 12 '13, 01:02 AM

Originally posted by Rabbit

No, that's not the full SQL, that's just an expression.

Sorry, Didn't realised it. This is what you are looking to check?.

Code:

SELECT 
  ([EqualizationToApproveAdvOT_1].[OT-StartTime]
      >=[EqualizationToApproveAdvOT].[OT-EndTime]) 
      Or ([EqualizationToApproveAdvOT_1].[OT-EndTime]
      <=[EqualizationToApproveAdvOT].[OT-StartTime]) 
      Or ([EqualizationToApproveAdvOT].[OfficersName]
      <>[EqualizationToApproveAdvOT_1].[OfficersName]) 
      Or ([EqualizationToApproveAdvOT].[OT_Date]
      <>[EqualizationToApproveAdvOT_1].[OT_Date]) 
      Or ([EqualizationToApproveAdvOT].[Overtime_lineNumber]
      =[EqualizationToApproveAdvOT_1].[Overtime_lineNumber])
      AS NoOTClash,
   EqualizationToApproveAdvOT.OfficersName,
   EqualizationToApproveAdvOT.Overtime_lineNumber,
   EqualizationToApproveAdvOT.OT_PP,
   EqualizationToApproveAdvOT.OT_PP_WeekNumber,
   EqualizationToApproveAdvOT.OT_Date,
   EqualizationToApproveAdvOT.[OT-StartTime],
   EqualizationToApproveAdvOT.[OT-EndTime],
   EqualizationToApproveAdvOT.TotalHoursOFOT,
   EqualizationToApproveAdvOT.Qualifications, 
   EqualizationToApproveAdvOT.TitleRank, 
   EqualizationToApproveAdvOT.[SumOfSumOfTotal OT Hrs], 
   EqualizationToApproveAdvOT.SeniorityRank, 
   EqualizationToApproveAdvOT.ApprovedOTLocation, 
   EqualizationToApproveAdvOT_1.OfficersName, 
   EqualizationToApproveAdvOT_1.Overtime_lineNumber, 
   EqualizationToApproveAdvOT_1.OT_PP, 
   EqualizationToApproveAdvOT_1.OT_PP_WeekNumber, 
   EqualizationToApproveAdvOT_1.OT_Date, 
   EqualizationToApproveAdvOT_1.[OT-StartTime], 
   EqualizationToApproveAdvOT_1.[OT-EndTime], 
   EqualizationToApproveAdvOT_1.TotalHoursOFOT, 
   EqualizationToApproveAdvOT_1.Qualifications, 
   EqualizationToApproveAdvOT_1.TitleRank, 
   EqualizationToApproveAdvOT_1.[SumOfSumOfTotal OT 
   Hrs],
   EqualizationToApproveAdvOT_1.SeniorityRank, 
   EqualizationToApproveAdvOT_1.ApprovedOTLocation
FROM 
   EqualizationToApproveAdvOT, 
   EqualizationToApproveAdvOT 
      AS EqualizationToApproveAdvOT_1
WHERE (
   ((([EqualizationToApproveAdvOT_1].[OT-StartTime]
      >=[EqualizationToApproveAdvOT].[OT-EndTime]) 
   Or ([EqualizationToApproveAdvOT_1].[OT-EndTime]
      <=[EqualizationToApproveAdvOT].[OT-StartTime]) 
   Or ([EqualizationToApproveAdvOT].[OfficersName]
      <>[EqualizationToApproveAdvOT_1].[OfficersName])
   Or ([EqualizationToApproveAdvOT].[OT_Date]
      <>[EqualizationToApproveAdvOT_1].[OT_Date]) 
   Or ([EqualizationToApproveAdvOT].[Overtime_lineNumber]
      =[EqualizationToApproveAdvOT_1].[Overtime_lineNumber]))
   =False));

**Rabbit** · Aug 12 '13, 05:49 AM

You shouldn't do a Cartesian product like that, it makes the where clause too complicated to work out. You need to first join the tables correctly and then use the where clause to look for overlap only, not to simulate your join.

**Tamakm** · Aug 13 '13, 02:56 AM

Originally posted by Rabbit

You shouldn't do a Cartesian product like that, it makes the where clause too complicated to work out. You need to first join the tables correctly and then use the where clause to look for overlap only, not to simulate your join.

Thank you for taking time to respond. I was really looking for help and understanding to write the code.

**Rabbit** · Aug 13 '13, 04:42 AM

If there's something you don't understand in what I said, tell me which part and I'll explain it.

Conflicting time for work schedule

Conflicting time for work schedule

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment