Using FTSearch with date fields

Question

I have code that will search particular fields in the document to find the values that the user selects and places those documents in a folder. I have the code working, but I'm stuck on how to proceed with doing the same thing with date fields. I need to be able to find documents within a date range. Can anyone help me with how to go about doing this? Here is the code that I have:

Sub Initialize
    On Error GoTo ErrHandler
    Dim ws As New NotesUIWorkspace
    Dim session As New NotesSession
    Dim db As NotesDatabase
    Dim RemoveView As NotesView
    Dim RemoveDoc As NotesDocument
    Dim RemoveEntry As NotesViewEntryCollection 
    Dim dc As NotesDocumentCollection
    Dim doc As NotesDocument
    Dim view As NotesView
    Dim j As Integer
    Dim DialogDoc As NotesDocument

    Set db = session.CurrentDatabase

    Folder$ = "(UseOfForceSearch)"

    'Clear folder before inputting current search results.
    Set RemoveView = db.GetView(Folder$)
    Set RemoveDoc = RemoveView.GetFirstDocument

    If Not (RemoveDoc Is Nothing) Then
        Set RemoveEntry = RemoveView.AllEntries
        Call RemoveEntry.RemoveAllFromFolder(Folder$)
    End If

    If Not (db.IsFTIndexed) Then
        answer = MessageBox(" Sorry. This database is not full-text indexed." + "Do you want to create an index?", MB_YESNO)
        If (answer = IDYES) Then
            Call db.UpdateFTIndex(True)
        End If
    End If 
    Set DialogDoc = db.Createdocument

    searchForm$ = "$Force"
    Form$ = "ReportDialog"  'Ask user for specifics of search using ReportDialog
    Title$ = "Use of Force Search Criteria"
    DialogDoc.Form = Form$


    DialogDoc.StartDate = DialogDoc.StartDate(0)
    DialogDoc.EndDate = DialogDoc.EndDate(0)
    DialogDoc.Field1 = DialogDoc.Field1(0)
    DialogDoc.Value1 = DialogDoc.Value1(0)
    DialogDoc.Field2 = DialogDoc.Field2(0)
    DialogDoc.Value2 = DialogDoc.Value2(0)
    DialogDoc.Field3 = DialogDoc.Field3(0)
    DialogDoc.Value3 = DialogDoc.Value3(0)
    DialogDoc.Field4 = DialogDoc.Field4(0)
    DialogDoc.Value4 = DialogDoc.Value4(0)
    DialogDoc.Field5 = DialogDoc.Field5(0)
    DialogDoc.Value5 = DialogDoc.Value5(0)
    DialogDoc.Logic1 = DialogDoc.Logic1(0)

EnterDialog:
    If (ws.DialogBox(Form$, True, True, False, True, False, False, Title$,DialogDoc,True)) Then
    Else
        Exit Sub
    End If

    SDate$ = CStr(DialogDoc.StartDate(0))
    EDate$ = CStr(DialogDoc.EndDate(0))

    Field1$ = UCase(CStr(DialogDoc.Field1(0)))
    Field2$ = UCase(CStr(DialogDoc.Field2(0)))
    Field3$ = UCase(CStr(DialogDoc.Field3(0)))
    Field4$ = UCase(CStr(DialogDoc.Field4(0)))
    Field5$ = UCase(CStr(DialogDoc.Field5(0)))

    Oper$ = "contains"

    Value1$ = UCase(CStr(DialogDoc.Value1(0)))
    Value2$ = UCase(CStr(DialogDoc.Value2(0)))
    Value3$ = UCase(CStr(DialogDoc.Value3(0)))
    Value4$ = UCase(CStr(DialogDoc.Value4(0)))
    Value5$ = UCase(CStr(DialogDoc.Value5(0)))

    Logic1$ = UCase(CStr(DialogDoc.Logic1(0)))
    Logic2$ = UCase(CStr(DialogDoc.Logic2(0)))
    Logic3$ = UCase(CStr(DialogDoc.Logic3(0)))
    Logic4$ = UCase(CStr(DialogDoc.Logic4(0)))

    Set Date1 = New NotesDateTime(SDate$)
    Set Date2 = New NotesDateTime(EDate$)
    Date1ForReport$ = Date1.DateOnly 
    Date2ForReport$ = Date2.DateOnly 

    datemax = Date2.timedifference(Date1)           
    DaysBetween = datemax \ 86400       
    If(DaysBetween > (366 * 1)) Then        
        MessageBox "Date range cannot exceed 1 year.",48,"Error:"
        GoTo EnterDialog
    End If  

    For i = 1 To 1
        searchForm$ = "(FIELD Form contains $Force) and "
        Expr1$ = "FIELD " + Field1$ + " " + Oper$ + " " + Value1$
        Expr2$ = "FIELD " + Field2$ + " " + Oper$ + " " + Value2$
        Expr3$ = "FIELD " + Field3$ + " " + Oper$ + " " + Value3$
        Expr4$ = "FIELD " + Field4$ + " " + Oper$ + " " + Value4$
        Expr5$ = "FIELD " + Field5$ + " " + Oper$ + " " + Value5$

        FinalExpr$ = searchForm$ + Expr1$

        If(Logic1$ <> "") Then
            FinalExpr$ = FinalExpr$ + " " + Logic1$ + " " + Expr2$
        ElseIf (Logic2$ <> "") Then
            FinalExpr$ = FinalExpr$ + " " + Logic1$ + " " + Expr2$ + " " + Logic2$ + " " + Expr3$
        ElseIf (Logic3$ <> "") Then
            FinalExpr$ = FinalExpr$ + " " + Logic1$ + " " + Expr2$ + " " + Logic2$ + " " + Expr3$ + " " + Logic3$ + " " + Expr4$
        ElseIf (Logic4$ <> "") Then
            FinalExpr$ = FinalExpr$ + " " + Logic1$ + " " + Expr2$ + " " + Logic2$ + " " + Expr3$ + " " + Logic3$ + " " + Expr4$ + " " + Logic4$ + " " + Expr5$
        End If  
        FinalExpr$ = FinalExpr$ 
        Print "Searching..."
        ' the number 16384 means fuzzy search
        Set dc = db.FTSearch(FinalExpr$,0,,16384)   
        Folder$ =  "(UseOfForceSearch)"     
        Call dc.PutAllInFolder(Folder$,True)
    Next    
    Print "Search Completed with " + CStr(dc.Count) + " results."   
    Set view = db.getView(Folder$)              
    Exit Sub
ErrHandler:
    MessageBox "Error" & Str(Err) & ": " &  Error$,16,"Error!"
    Exit Sub
End Sub

Using FTSearch with date fields

Answers (1)

Related Questions