Please HELP!!! Checking for Conflicting Dates

[bjnorth]

Hello All,

I am a fairly new to Access but have been muddling along. I have an Access project which is due early next week and am having a pain in the *ss problem which I've tried to fix but it's still not working. This database is for our Sheriff's Office. They want to track the types of calls they receive and a whole bunch of other stuff. This is how it's SUPPOSED to work. The user opens the database and logs in. The Main Menu comes up. The user clicks on the My Time In/Time Out button which opens the MyWorkLogF form and enters his time for a particular day (military time). He exits out of that form and from the Main Menu clicks on the Add My Calls button which opens the Calls form. There are two date fields and two time fields on the form. After the user enters the info into these fields, I want the system to check the qryWorkLog to make sure the user is actually on shift for the date and times he entering for the Calls. He can enter many calls for one date. However, the system needs to check that the CallTimeReceived and CallTimeCleared doesn't overlap on any calls.
Worklog Entry: Date In: 02/10/14 Time In: 07:00 Date Out: 02/10/14 Time Out: 17:00
Example: Call Date Received: 02/10/14
Call Time Received: 07:30
Call Date Cleared: 02/10/14
Call Time Cleared: 09:30

For the next call entry if the user tried to enter:
Call Date Received: 02/10/14
Call Time Received: 08:00
Call Date Cleared: 02/10/14
Call Time Cleared: 09:00

The system shouldn't allow the 2nd call entry because the 1st call wasn't cleared yet. They can't be on two different calls at the same time.

When I try to enter a call the system just keeps telling me I'm 'Not on Shift' no matter what times I put in the form. I hope I have explained this well enough so that anyone reading this will no what the heck I'm talking about. I'm not able to upload the database cuz it's too big. If someone out there wants to check it out, I will email it to you. It's only a bit larger than 1.0 mb. The following is the code I'm using:

Private Sub CallDateReceived_AfterUpdate()
    CheckConflict
    CheckCallConflict
End SubPrivate Sub CallTimeCleared_AfterUpdate()
    CheckTime
    CheckConflict
    CheckCallConflict
End Sub
Private Sub CallTimeReceived_AfterUpdate()
    CheckTime
    CheckConflict
    CheckCallConflict
End Sub
Sub CheckCallConflict()
    
    If CheckDateTimes = False Then
        Exit Sub
    End If
    
    Dim ID As Long
    ID = 0
    Dim ID2 As Long
    ID2 = 0
    Dim ID3 As Long
    ID3 = 0
    
    'Join the Date and Times
    Dim CallReceived As Date
    Dim CallCleared As Date
    'CallReceived = DateValue(CallDateReceived) + DateValue(CallTimeReceived)
    CallReceived = Format([CallDateReceived], "Short Date") & " " & Format([CallTimeReceived], "Long Time")
    'Format for searching
    CallReceived = Format(CallReceived, "dd/mm/yyyy HH:nn:ss")
    'CallCleared = DateValue(CallDateCleared) + DateValue(CallTimeCleared)
    CallCleared = Format([CallDateCleared], "Short Date") & " " & Format([CallTimeCleared], "Long Time")
    'Format for searching
    CallCleared = Format(CallCleared, "dd/mm/yyyy HH:nn:ss")
    
    'Check if the EmployeeID matches then the Date/Times for Start and End
    ID = Nz(DLookup("CallID", "qryCalls", _
        "EmployeeID=" & EmployeeID & " AND " & _
        "CallID <> " & CallID & " AND " & _
        "CallReceived <= #" & CallReceived & "# AND " & _
        "CallCleared > #" & CallReceived & "#"), 0)
    
    ID2 = Nz(DLookup("CallID", "qryCalls", _
        "EmployeeID=" & EmployeeID & " AND " & _
        "CallID <> " & CallID & " AND " & _
        "CallReceived < #" & CallCleared & "# AND " & _
        "CallCleared > #" & CallCleared & "#"), 0)
    
    ID3 = Nz(DLookup("CallID", "qryCalls", _
        "EmployeeID=" & EmployeeID & " AND " & _
        "CallID <> " & CallID & " AND " & _
        "CallReceived = #" & CallCleared & "# AND " & _
        "CallCleared = #" & CallCleared & "#"), 0)
    
    If ID <> 0 Or ID2 <> 0 Or ID3 <> 0 Then
        MsgBox "Call already taken"
        DoCmd.RunCommand acCmdUndo
        CallTimeReceived.SetFocus
    Else
        MsgBox "OK Call"
    End If
End Sub
Sub CheckTime()
    
    If IsNull(CallTimeReceived) Or CallTimeReceived = "" Then
        Exit Sub
    ElseIf IsNull(CallTimeCleared) Or CallTimeCleared = "" Then
        Exit Sub
    Else
        If CallTimeReceived > CallTimeCleared Then
            MsgBox "'CallTimeReceived' can't be greater than 'CallTimeCleared'"
            DoCmd.RunCommand acCmdUndo
            CallTimeReceived.SetFocus
        End If
    End If
    
End Sub
Sub CheckConflict()
    
    If CheckDateTimes = False Then
        Exit Sub
    End If
    
    Dim ID As Long
    ID = 0
    
    'Join the Date and Times
    Dim CallReceived As Date
    Dim CallCleared As Date
    'CallReceived = DateValue(CallDateReceived) + DateValue(CallTimeReceived)
    CallReceived = Format([CallDateReceived], "Short Date") & " " & Format([CallTimeReceived], "Long Time")
    'Format for searching
    CallReceived = Format(CallReceived, "mm/dd/yyyy HH:nn:ss")
    'CallCleared = DateValue(CallDateCleared) + DateValue(CallTimeCleared)
    CallCleared = Format([CallDateCleared], "Short Date") & " " & Format([CallTimeCleared], "Long Time")
    'Format for searching
    CallCleared = Format(CallCleared, "mm/dd/yyyy HH:nn:ss")
    
    'Check if the EmployeeID matches then the Date/Times for Start and End
    ID = Nz(DLookup("WorkLogID", "qryWorkLog", _
        "EmployeeID=" & EmployeeID & " AND " & _
        "DateTimeIn <= #" & CallReceived & "# AND " & _
        "DateTimeOut >= #" & CallReceived & "# AND " & _
        "DateTimeIn <= #" & CallCleared & "# AND " & _
        "DateTimeOut >= #" & CallCleared & "#"), 0)
    
    If ID <> 0 Then
        MsgBox "OK Shift"
    Else
        MsgBox "Not on shift"
        DoCmd.RunCommand acCmdUndo
        CallTimeReceived.SetFocus
        'CallTimeReceived.Text = "" '= Null
        'CallTimeCleared = "" '= Null
    End If
End Sub
Function CheckDateTimes() As Boolean
    
    CheckDateTimes = True
    
    If IsNull(CallDateReceived) Or CallDateReceived = "" Then
        CheckDateTimes = False
    ElseIf IsNull(CallTimeReceived) Or CallTimeReceived = "" Then
        CheckDateTimes = False
    ElseIf IsNull(CallDateCleared) Or CallDateCleared = "" Then
        CheckDateTimes = False
    ElseIf IsNull(CallTimeCleared) Or CallTimeCleared = "" Then
        CheckDateTimes = False
    End If
End Function

A ton of thanks to anyone who attempts to help solve this issue from hel*.

BJ

[June7]

Have you step debugged? Follow the code as it executes to find where it deviates from expected behavior, fix and debug again.

Review to link at bottom of my post for debugging guidelines.

[bjnorth]

I've read the stuff on cpearson.com and tried to debug it but it didn't do anything. Once I get the 'Not on Shift' message box the date and time fields revert back to the default. So, I'm not even sure how to make it debug. But, thanks.

[orange]

Put a breakpoint early in the code logic.
When execution stops it will point to the next line to be executed.
Use the F8 key to move 1 line at a time.
Put your cursor over some variables to see the value.

In the immediate window you can use ?variable [return/enter] and it will display the value or an error msg

It didn't do anything

is not a descriptive answer to help us help you

Don't give up!

[June7]

Did you set a breakpoint at the beginning of procedure? Did you step one line at time?

That's a lot of code to read through. I can't even find the message box you refer to.

[bjnorth]

In the code, I changed all of the "Long Time" formats to "Short Time" and the HH:mm:ss to HH:mm and at least the system recognizes I am on shift and accepts the Call entry. However, it's still allowing me to enter overlapping call times. Not sure why.

[bjnorth]

Sorry Orange....like I said....I'm kinda new at this stuff. Thanks

[June7]

A time part without date part can produce unexpected results, especially if range crosses midnight. I don't know that is the case, just keep it in mind.

If you have to develop and maintain programmed database, debugging this procedure will be an invaluable experience. It's hard for us to do this for you by just reading the code and it doesn't trigger run-time errors that would direct us to a problem area, you just aren't getting the desired results so you have to keep checking your logic by stepping through.

[orange]

Believe me Debug works and does something.

You seem to have a logic problem or some sort of corruption.
You should not have to change formats in order to make debugging work.
Put yourself in our position, we don't know your situation nearly as well as you. We depend on you to communicate things to us. So when you say "It didn't do anything.." that's just about useless.

Use MsgBox or Debug.Print statements while testing to
a) verify the contents of variables
b) monitor the execution of the code

Always put errorHandling in your procedures. You may think you know it won't fail, but you won't always be correct.

Did you ever confirm you were/were not On Shift?
You know with a break point you can change the value of a field and continue execution.

Debugging is key to resolving logic issues.

Have you checked and rechecked the code to prevent overlapping call times?

This may be useful