if Data exist for an Emp. ID who already submitted same type leave between two dates

**Seth Schrock** · Jan 21 '16, 01:51 PM

You will actually have to perform two tests, one for Start_Date and one for End_Date. I would probably just do two DCount() functions. So something like this (air code):

Code:

Dim strStartCriteria As String
Dim strEndCriteria As String

strStartCriteria = "eeID = " & Me.eID & " And Emp_LeaveType = '" & Me.L_Type & _
                   "' And Leave_From <= #" & Me.Start_Date & "# And Leave_Upto > #" & Me.Start_Date & "#"
strEndCriteria = "eeID = " & Me.eID & " And Emp_LeaveType = '" & Me.L_Type & _
                 "' And Leave_From <= #" & Me.End_Date & "# And Leave_Upto > #" & Me.End_Date & "#"

If DCount("*", "tabLeave", strStartCriteria) > 0 Or DCount("*", "tabLeave", strEndCriteria) > 0 Then
    'Duplicated leave
End If

**jforbes** · Jan 21 '16, 01:52 PM

I would create a Function that tests for the overlap and put it in the BeforeUpdate Event of the Form, having it Cancel the Save if there is an overlap.

I think you can make the function out of a few DCounts. I would looks something like the following. This is air code, you'll have to take ownership of it:

Code:

Public Function testOverlap() As Boolean

    Dim dLeave_From As Date
    Dim dLeave_Upto As Date
    Dim leeID As Long
    
    If IsDate(Me!Leave_From) And IsDate(Me!Leave_Upto) Then
        leeID = Me!eeID
        dLeave_From = Me!Leave_From
        dLeave_Upto = Me!Leave_Upto
        
        ' Default Test to Passing, then look failure points
        testOverlap = True
        If testOverlap Then If DCount("eeID", "tabLeave", "Leave_From<#" & dLeave_From & "# AND dLeave_Upto>#" & dLeave_From & "#") Then testOverlap = False
        If testOverlap Then If DCount("eeID", "tabLeave", "Leave_From<#" & Leave_Upto & "# AND dLeave_Upto>#" & Leave_Upto & "#") Then testOverlap = False
        '...
        ' Any other testing you want to perform
        '...
    Else
        testOverlap = False
    End If
    
End Function

HAHA, Crossposted with Seth, good times, good times.

**mukeshpiplai** · Jan 21 '16, 02:44 PM

Thanks to Seth and jforbes for reply

you both not added LeaveEntryType in your formula..may be i Forgot it to mention.
LeaveEntryType is also a text box in table and form which describes about Leave Entry is monthly, PUC, Adjustment etc.

So I tried following code of Seth with above field amendment but It not worked. If I tried formula of "Seth" without LeaveEntryType then it works fine for me. So kindly solve my issue with adding above field..

Here's my code.

Code:

'My other coding

Else
strStartCriteria = "eeID = " & Me.LeID & " And Emp_LeaveType = '" & Me.L_Type & " And LeaveEntryType = '" & Me.LeaveEntryType & _
                   "' And Leave_From <= #" & Me.Start_Date & "# And Leave_Upto > #" & Me.Start_Date & "#"
strEndCriteria = "eeID = " & Me.LeID & " And Emp_LeaveType = '" & Me.L_Type & " And LeaveEntryType = '" & Me.LeaveEntryType & _
                 "' And Leave_From <= #" & Me.End_Date & "# And Leave_Upto > #" & Me.End_Date & "#"
 
    If DCount("*", "tabLeave", strStartCriteria) > 0 Or DCount("*", "tabLeave", strEndCriteria) > 0 Then
        MsgBox " Duplicate Leave"
        Me.End_Date = ""
        Me.End_Date.SetFocus
    Else
        Me!NoDays.Value = Nz((End_Date - Start_Date), 0) + 1
        Me!Leav_Close.Value = Nz(([Leav_Open] - [NoDays]), 0)
    End If
End If

**Seth Schrock** · Jan 21 '16, 03:06 PM

Sorry, I guess I read entry type and confused that with the leave type. Here would be the criteria then.

Code:

strStartCriteria = "eeID = " & Me.eID & " And Emp_LeaveType = '" & Me.L_Type & _
                   "' And Leave_From <= #" & Me.Start_Date & "# And Leave_Upto > #" & Me.Start_Date & "#" & _
                   " And LeaveEntryType = '" & Me.EntryType & "'"
strEndCriteria = "eeID = " & Me.eID & " And Emp_LeaveType = '" & Me.L_Type & _
                 "' And Leave_From <= #" & Me.End_Date & "# And Leave_Upto > #" & Me.End_Date & "#" & _
                 " And LeaveEntryType = '" & Me.EntryType & "'"

**mukeshpiplai** · Jan 21 '16, 03:13 PM

Sorry to post... I tried after correct in above code and its works fine... but there a problem.

Problem is that an employee already applied a leave type for 01-Jan-16 to 05-Jan-16.. now I goes to apply for leave from 01-Jan-16 to any further date then code prevent it. but it not prevent if I applied 02-Jan-16 to any future date Or 30-Dec-15 to any future date. This code works only if My start date is same with existing database date for above criteria.

So need some guidelines in this regard.

Thanks again.

**mukeshpiplai** · Jan 21 '16, 03:20 PM

My full code is following:-

Code:

Private Sub End_Date_AfterUpdate()

On Error GoTo ErrorHandler

Dim strStartCriteria As String
Dim strEndCriteria As String
Dim HPLBal As String
Dim StartDt As Date
Dim EndDt As Date

If IsNull(L_Type) Then
MsgBox " Enter Leave Type"
End_Date = ""
Exit Sub

ElseIf IsNull(Start_Date) Then
MsgBox " Enter Leave Starting Date"
End_Date = ""
Exit Sub

ElseIf End_Date < Start_Date Then
MsgBox " Leave End Date can't be Less then Leave starting Date...Kindly check."
End_Date = ""
Exit Sub

End If


NoDays = Nz((End_Date - Start_Date), 0) + 1
Leav_Close = Nz(([Leav_Open] - [NoDays]), 0)

If Leav_Open < NoDays Or Leav_Close < 0 Or Leav_Close < -1 Then
MsgBox " No sufficient Leave Balance...Kindly Check Or Update Leave Balance"
End_Date = ""
NoDays = ""
Leav_Close = ""
Exit Sub



Else
strStartCriteria = "eeID = " & Me.LeID & " And Emp_LeaveType = '" & Me.L_Type & _
                   "' And Leave_From <= #" & Me.Start_Date & "# And Leave_Upto > #" & Me.Start_Date & "#" & _
                   " And LeaveEntryType = '" & Me.LeaveEntryType & "'"
strEndCriteria = "eeID = " & Me.LeID & " And Emp_LeaveType = '" & Me.L_Type & _
                 "' And Leave_From <= #" & Me.End_Date & "# And Leave_Upto > #" & Me.End_Date & "#" & _
                 " And LeaveEntryType = '" & Me.LeaveEntryType & "'"
 
    If DCount("*", "tabLeave", strStartCriteria) > 0 Or DCount("*", "tabLeave", strEndCriteria) > 0 Then
        MsgBox " This Leave period overlaped with existing Leave in database for this employee with this type of Leave. Kindly check previous record of this employee."
        Me.End_Date = ""
        Me.Start_Date = ""
        Me.NoDays = ""
        Me.Leav_Close = ""
        Me.End_Date.SetFocus
    Else
        Me!NoDays.Value = Nz((End_Date - Start_Date), 0) + 1
        Me!Leav_Close.Value = Nz(([Leav_Open] - [NoDays]), 0)
    End If
End If

If L_Type = "Commuted Leave" Then

HPLBal = Nz((TempVars!HPL.Value), 0)
Me!NoDays.Value = Nz((End_Date - Start_Date), 0) + 1
Me!Leav_Close.Value = Nz((HPLBal - ([NoDays] * 2)), 0)
MsgBox "Kindly read " & Leav_Close & " Half Pay Leave Balance instead of " & Leav_Close & " Commuted Leave after this leave period."

End If

ExitSub:
    Exit Sub
ErrorHandler:
    Resume ExitSub


End Sub

**Seth Schrock** · Jan 21 '16, 03:47 PM

Please place the following lines of code between lines 47 and 49:

Code:

Debug.Print "SCr: " & strStartCriteria
Debug.Print "ECr: " & strEndCriteria
Debug.Print "SCo: " & DCount("*", "tabLeave", strStartCriteria)
Debug.Print "ECo: " & DCount("*", "tabLeave", strEndCriteria)

This will put some output in your Immediate window of the code editor (press Ctrl + G if not visible). Please post the results back to this thread so that we can see what is going on.

Also, please verify that the EmployeeID, Entry Type and Leave type are all matching the existing leave record.

**mukeshpiplai** · Jan 21 '16, 05:56 PM

There is following result if Start Date is 30-Dec-15 and End Date 02-Jan-16... There's already leave with same details with Leave Period 01-Jan-16 to 05-Jan-16...so now code should prevent duplicate value. but it not works with Start Date 30-Dec-15 and End Date 02-Jan-16... It works good with Start Date 01-Jan-16 and End Date 02-Jan-16....or any future date...

Immediate window result is:

Code:

SCr: eeID = 112233 And Emp_LeaveType = 'Earned Leave' And Leave_From <= #30-12-2015# And Leave_Upto > #30-12-2015# And LeaveEntryType = 'PUC'
ECr: eeID = 112233 And Emp_LeaveType = 'Earned Leave' And Leave_From <= #03-01-2016# And Leave_Upto > #03-01-2016# And LeaveEntryType = 'PUC'
SCo: 0
ECo: 0

**Seth Schrock** · Jan 21 '16, 09:09 PM

I believe that this should fix the problem. Change strStartCriteri a to the following:

Code:

strStartCriteria = "eeID = " & Me.LeID & " And Emp_LeaveType = '" & Me.L_Type & _
                   "' And Leave_From <= #" & Me.Start_Date & "# And Leave_Upto > #" & Me.Start_Date & "#" & _
                   " And LeaveEntryType = '" & Me.LeaveEntryType & "'"
				   " And (#" & Me.Start_Date & "# Between Leave_From And Leave_Upto" & _
				   " Or Leave_From Between #" & Me.Start_Date & "# And #" & Me.End_Date & "#" & _
				   " Or Leave_Upto Between #" & Me.Start_Date & "# And #" & Me.End_Date & "#" & _
				   " Or #" & Me.End_Date & "# Between Leave_From And Leave_Upto)"

Now on line 49 of your post, remove the second DCount() test. I combined them into one so it should work that way.

**mukeshpiplai** · Jan 22 '16, 04:20 AM

Not working respected seth-
Now immediate window reply is following:-

Code:

SCr: eeID = 112233 And Emp_LeaveType = 'Earned Leave' And Leave_From <= #30-12-2015# And Leave_Upto > #30-12-2015# And LeaveEntryType = 'PUC' And (#30-12-2015# Between Leave_From And Leave_Upto Or Leave_From Between #30-12-2015# And #02-01-2016# Or Leave_Upto Between #30-12-2015# And #02-01-2016# Or #02-01-2016# Between Leave_From And Leave_Upto)
SCo: 0

**mukeshpiplai** · Jan 22 '16, 05:53 AM

Here's problem-

Code:

SCr: eeID = 112233 And Emp_LeaveType = 'Earned Leave' And LeaveEntryType = 'PUC' And (#01-01-2016# Between Leave_From And Leave_Upto Or Leave_From Between #01-01-2016# And #02-01-2016# Or Leave_Upto Between #01-01-2016# And #02-01-2016# Or #02-01-2016# Between Leave_From And Leave_Upto)
SCo: 1
SCr: eeID = 112233 And Emp_LeaveType = 'Earned Leave' And LeaveEntryType = 'PUC' And (#02-01-2016# Between Leave_From And Leave_Upto Or Leave_From Between #02-01-2016# And #03-01-2016# Or Leave_Upto Between #02-01-2016# And #03-01-2016# Or #03-01-2016# Between Leave_From And Leave_Upto)
SCo: 0

Here code gives result in 1 if start date is 01-01-2016 but result 0 if start date is 02-01-2016. Leave period in database is from 01-01-2016 to 05-01-2016 then second date [02-01-2016] should give result in 1 also . But there's some problem which I am not understanding.. ..

**Seth Schrock** · Jan 22 '16, 01:48 PM

Try this.

Code:

strStartCriteria = "eeID = " & Me.LeID & " And Emp_LeaveType = '" & Me.L_Type & _
                   " And LeaveEntryType = '" & Me.LeaveEntryType & "'"
				   " And (#" & Me.Start_Date & "# Between Leave_From And Leave_Upto" & _
				   " Or Leave_From Between #" & Me.Start_Date & "# And #" & Me.End_Date & "#" & _
				   " Or Leave_Upto Between #" & Me.Start_Date & "# And #" & Me.End_Date & "#" & _
				   " Or #" & Me.End_Date & "# Between Leave_From And Leave_Upto)"

I forgot to remove one of the lines of code.

**mukeshpiplai** · Jan 22 '16, 04:23 PM

Thanks a lot Seth Schrok.... Code worked fine...

Thanks a lot

**Seth Schrock** · Jan 22 '16, 04:25 PM

Good! I was starting to pull my hair out :) Good luck on your project.

You might want to reset the best answer for any others who might be having the same problem.

if Data exist for an Emp. ID who already submitted same type leave between two dates

if Data exist for an Emp. ID who already submitted same type leave between two dates

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment