In re-designing a Leave Application database, it was essential that submitted Leave Start / End dates be unique for the person submitting the leave request, in other words, these dates do not cross over another leave request's start/end dates for this person.
The validation script contained a complext searchformula$ text strings (@Commands) passed on to a db.Search.
To over come this problem i formulated the "ICDC"
The ICDC can be amended to check other date conditions by simply changing the > < symbols. May also use <=, etc. depends on what it is you want to test for.
'.....check if there are any active or existing leave requests for date range, where dates cross over Start, End, & within. Dim view As notesview Dim check As notesdocument Set view = db.getView("
") If Not (view Is Nothing) Then Set check = view.getfirstDocument While Not (check Is Nothing) '..... if the personName in found doc is the same as as the name in current doc If check.name(0) = doc.name(0) Then Dim fromdateA As New notesdatetime(doc.LeavestartDate(0)) Dim toDateA As New notesdatetime(doc.LeaveEndDate(0)) '......do the ICDC stuff If ( Cdat(check.Leavestartdate(0)) < Cdat(toDateA.dateonly ) ) And ( Cdat(check.LeaveEndDate(0)) > Cdat(fromDateA.dateonly ) ) Then Messagebox "There is a Leave Request in place for the period requested." & Chr(13) & _ "Please review your dates.", 0+48, "Error" Exit Sub End If End If Set check = view.getnextdocument(check) Wend End If