DataView filter example

    Public Function FilterClicked() As Integer

        Dim startDateString As String = Nothing
        Dim startDateCombo As String = ThisForm.Components(Comps.StartDateCombo).Value
        If (startDateCombo <> "") Then
            Dim startDateDate As Date
            If (Date.TryParse(startDateCombo, startDateDate) = False) Then
                Return -1
            Else
                startDateString = startDateDate.ToString("yyyy-MM-dd")
            End If
        End If


        Dim endDateString As String = Nothing
        Dim endDateCombo As String = ThisForm.Components(Comps.EndDateCombo).Value
        If (endDateCombo <> "") Then
            Dim endDateDate As Date
            If (Date.TryParse(endDateCombo, endDateDate) = False) Then
                Return -1
            Else
                endDateString = endDateDate.ToString("yyyy-MM-dd")
            End If
        End If



        Dim sb As New Text.StringBuilder


        ' Date filter
        If (startDateString IsNot Nothing) AndAlso (endDateString IsNot Nothing) Then
            sb.AppendFormat("TransDate BETWEEN '{0}' AND '{1}'", startDateString, endDateString)
        ElseIf (startDateString IsNot Nothing) Then
            sb.AppendFormat("TransDate >= '{0}'", startDateString)
        ElseIf (endDateString IsNot Nothing) Then
            sb.AppendFormat("TransDate <= '{0}'", endDateString)
        End If


        ' Job filter
        Dim jobStart As String = ThisForm.Variables(Vars.JobStartVar).Value
        Dim jobEnd As String = ThisForm.Variables(Vars.JobEndVar).Value

        If (jobStart <> "") AndAlso (jobEnd <> "") Then

            If (jobStart = jobEnd) Then
                sb.AppendFormat(" AND Job = '{0}'", jobStart)


                Dim suffixStartString As String = ThisForm.Variables(Vars.SuffixStartVar).Value
                Dim suffixEndString As String = ThisForm.Variables(Vars.SuffixEndVar).Value

                Dim suffixStartInteger As Integer = If(suffixStartString = "", 0, CInt(suffixStartString))
                Dim suffixEndInteger As Integer = If(suffixEndString = "", 0, CInt(suffixEndString))

                ' integers, no single quotes!
                sb.AppendFormat(" AND Suffix BETWEEN {0} AND {1}", suffixStartInteger, suffixEndInteger)
            Else
                sb.AppendFormat(" AND Job BETWEEN '{0}' AND '{1}'", jobStart, jobEnd)
            End If

        ElseIf (jobStart <> "") Then
            sb.AppendFormat(" AND Job >= '{0}'", jobStart)
        ElseIf (jobEnd <> "") Then
            sb.AppendFormat(" AND Job <= '{0}'", jobEnd)
        End If



        ' Item filter
        Dim itemStart As String = ThisForm.Variables(Vars.ItemStartVar).Value
        Dim itemEnd As String = ThisForm.Variables(Vars.ItemEndVar).Value

        If (itemStart <> "" AndAlso itemEnd <> "") Then
            sb.AppendFormat(" AND Item BETWEEN '{0}' AND '{1}'", itemStart, itemEnd)
        ElseIf itemStart <> "" Then
            sb.AppendFormat(" AND Item >= '{0}'", itemStart)
        ElseIf itemEnd <> "" Then
            sb.AppendFormat(" AND Item <= '{0}'", itemEnd)
        End If


        ' Project filter
        Dim projNumStart As String = ThisForm.Variables(Vars.ProjNumStartVar).Value
        Dim projNumEnd As String = ThisForm.Variables(Vars.ProjNumEndVar).Value

        If (projNumStart <> "" AndAlso projNumEnd <> "") Then
            sb.AppendFormat(" AND ProjNum BETWEEN '{0}' AND '{1}'", projNumStart, projNumEnd)
        ElseIf projNumStart <> "" Then
            sb.AppendFormat(" AND ProjNum >= '{0}'", projNumStart)
        ElseIf projNumEnd <> "" Then
            sb.AppendFormat(" AND ProjNum <= '{0}'", projNumEnd)
        End If


        ' TaskNum filter...
        If (projNumStart = projNumEnd) Then

            Dim taskNumStart As String = ThisForm.Variables(Vars.TaskNumStartVar).Value
            Dim taskNumEnd As String = ThisForm.Variables(Vars.TaskNumEndVar).Value

            ' TaskNum type INT, no single quotes needed
            If (taskNumStart <> "" AndAlso taskNumEnd <> "") Then
                sb.AppendFormat(" AND TaskNum BETWEEN {0} AND {1}", taskNumStart, taskNumEnd)
            ElseIf taskNumStart <> "" Then
                sb.AppendFormat(" AND TaskNum >= {0}", taskNumStart)
            ElseIf taskNumEnd <> "" Then
                sb.AppendFormat(" AND TaskNum <= {0}", taskNumEnd)
            End If
        End If


        ' WorkCenter filter...
        Dim wcStart As String = ThisForm.Variables(Vars.WorkCenterStartVar).Value
        Dim wcEnd As String = ThisForm.Variables(Vars.WorkCenterEndVar).Value

        If (wcStart <> "" AndAlso wcEnd <> "") Then
            sb.AppendFormat(" AND Wc BETWEEN '{0}' AND '{1}'", wcStart, wcEnd)
        ElseIf wcStart <> "" Then
            sb.AppendFormat(" AND Wc >= '{0}'", wcStart)
        ElseIf wcEnd <> "" Then
            sb.AppendFormat(" AND Wc <= '{0}'", wcEnd)
        End If


        ' CostCode filter...
        Dim costCodeStart As String = ThisForm.Variables(Vars.CostCodeStartVar).Value
        Dim costCodeEnd As String = ThisForm.Variables(Vars.CostCodeEndVar).Value

        If costCodeStart <> "" AndAlso costCodeEnd <> "" Then
            sb.AppendFormat(" AND CostCode BETWEEN '{0}' AND '{1}'", costCodeStart, costCodeEnd)
        ElseIf costCodeStart <> "" Then
            sb.AppendFormat(" AND CostCode >= '{0}'", costCodeStart)
        ElseIf costCodeEnd <> "" Then
            sb.AppendFormat(" AND CostCode <= '{0}'", costCodeEnd)
        End If


        ' Shift filter...
        Dim shiftStart As String = ThisForm.Variables(Vars.ShiftStartVar).Value
        Dim shiftEnd As String = ThisForm.Variables(Vars.ShiftEndVar).Value

        If (shiftStart <> "" AndAlso shiftEnd <> "") Then
            sb.AppendFormat(" AND Shift BETWEEN '{0}' AND '{1}'", shiftStart, shiftEnd)
        ElseIf shiftStart <> "" Then
            sb.AppendFormat(" AND Shift >= '{0}'", shiftStart)
        ElseIf shiftEnd <> "" Then
            sb.AppendFormat(" AND Shift <= '{0}'", shiftEnd)
        End If


        ' Indirect filter...
        Dim indirectStart As String = ThisForm.Variables(Vars.IndCodeStartVar).Value
        Dim indirectEnd As String = ThisForm.Variables(Vars.IndCodeEndVar).Value

        If (indirectStart <> "" AndAlso indirectEnd <> "") Then
            sb.AppendFormat(" AND IndCode BETWEEN '{0}' AND '{1}'", indirectStart, indirectEnd)
        ElseIf indirectStart <> "" Then
            sb.AppendFormat(" AND IndCode >= '{0}'", indirectStart)
        ElseIf indirectEnd <> "" Then
            sb.AppendFormat(" AND IndCode <= '{0}'", indirectEnd)
        End If

        ' Deptartment filter...
        Dim deptStart As String = ThisForm.Variables(Vars.DeptStartVar).Value
        Dim deptEnd As String = ThisForm.Variables(Vars.DeptEndVar).Value

        If (deptStart <> "" AndAlso deptEnd <> "") Then
            sb.AppendFormat(" AND Dept BETWEEN '{0}' AND '{1}'", deptStart, deptEnd)
        ElseIf deptStart <> "" Then
            sb.AppendFormat(" AND Dept >= '{0}'", deptStart)
        ElseIf deptEnd <> "" Then
            sb.AppendFormat(" AND Dept <= '{0}'", deptEnd)
        End If


        ThisForm.Variables(Vars.SSSWBSuperGridDataViewFilter).Value = sb.ToString
        ThisForm.Variables(Vars.SSSWBSuperGridMode).Value = "View"
        ThisForm.Variables(Vars.SSSWBSuperGridLayoutName).Value = ""

        Return 0
    End Function