how to validate the record of start date and end date when apply leave??

[jocelyn_ooi]

The following is the code i written. but there got a problem... if i already apply leave e.g 26-09-2011 till 29/09/2011. after that i apply again 28-09-2011. suppose there will pop out msg "You have already applied this leave before" but my code is unable to do so.. can somebody help me here???


' validate leave taken
' set all value to null
varDay = Null
varSameStartDate = Null
varSameEndDate = Null
varStartDate = Null
varEndDate = Null
varSameHalfday = Null

' search for same leave start date and end date
varSameStartDate = Nz(DCount("*", "tblLeaveTaken", "emp_id = '" & Me.txt_emp_id & "' " & _
" AND tkl_start_date = #" & Format(Me.txt_start_date, "mm/dd/yyyy") & "#"), 0)
varSameEndDate = Nz(DCount("*", "tblLeaveTaken", "emp_id = '" & Me.txt_emp_id & "' " & _
" AND tkl_end_date = #" & Format(Me.txt_end_date, "mm/dd/yyyy") & "#"), 0)

varStartDate = Nz(DCount("*", "tblLeaveTaken", "emp_id = '" & Me.txt_emp_id & "' " & _
" AND tkl_end_date = #" & Format(Me.txt_start_date, "mm/dd/yyyy") & "#"), 0)
varEndDate = Nz(DCount("*", "tblLeaveTaken", "emp_id = '" & Me.txt_emp_id & "' " & _
" AND tkl_start_date = #" & Format(Me.txt_end_date, "mm/dd/yyyy") & "#"), 0)

varSameHalfday = Nz(DCount("*", "tblLeaveTaken", "emp_id = '" & Me.txt_emp_id & "' " & _
" AND tkl_start_date = #" & Format(Me.txt_start_date, "mm/dd/yyyy") & "# " & _
" AND tkl_leave_type = '" & Me.cmb_leave_type & _
"' AND tkl_halfday_option = '" & Me.cmb_halfday_option & "'"), 0)

'validate same record
If ((varStartDate > 0) Or (varSameStartDate > 0) Or (varEndDate > 0) Or (varSameEndDate > 0)) And ((IsNull(Me.cmb_halfday_option) Or Me.cmb_halfday_option = "") Or (varSameHalfday > 0)) Then
MsgBox "You have already applied this leave before !", vbExclamation
Forms!frmLeaveApply!cmdBack.SetFocus
GoTo cmdApply_Click_Exit
End If

 

[pbaldy]

Does this help conceptually?

http://www.baldyweb.com/OverLap.htm

 

[jocelyn_ooi]

thx for ur info... but seem lik still can't filter the date i ald applied... still hav any idea??

 

[pbaldy]

What exactly did you try?