none
Checkbox to select multiple fields to search RRS feed

  • Question

  • I have a form that searches any/all currency fields using 1 option group to select the field to search, a second option group to select the search format (equal to, less than, etc) and a text box that will allow the user to input the amount to base the search on.  There's also a range search, but that's not an issue.
    Currently, I have all functions working except for the last option in option group 1, which is a "Search All" selection that will perform the search in all the optional fields.  I'm using the form filter to perform the search, also.  I understand that the SQL string being passed to the filter does not match the correct syntax of "((([Field1] = Amount)) OR (((Field2] = Amount)" and that's my problem.  How do I get the SQL correct (whether that matches my example or not) so that all the currency fields are searched?  Here's my code:

    Private Sub cmdSearch_Click()
    Dim StrOp As String
        Dim strSQL As String
        Dim strList As String
       
    Select Case (Me.frmFields)
        Case 1
            strList = "[Cost]"
        Case 2
            strList = "[SubTotalCost]"
        Case 3
            strList = "[DesignerFeeCost]"
        Case 4
            strList = "[ProjectManagementFeeCost]"
        Case 5
            strList = "[TotalFeeCost]"
        Case 6
            strList = "[TotalCost]"
        Case 7
            strList = "([Cost] OR [SubTotalCost] OR [DesignerFeeCost] OR [ProjectManagementFeeCost] OR [TotalFeeCost] OR [TotalCost])"
    End Select
    
        Select Case (Me.frmCompOperators)
            Case 1
                StrOp = " = "
            Case 2
                StrOp = " < "
            Case 3
                StrOp = " > "
            Case 4
                StrOp = " <= "
            Case 5
                StrOp = " >= "
        End Select
       
        
    If Not IsNull(Me.txtAmount1) Then
        strSQL = strList & StrOp & Me.txtAmount1
    Else
    If Not IsNull(Me.txtAmount2) Then
        strSQL = strList & " >=  " & Me.txtAmount2 & " AND " & strList & " < " & Me.txtAmount3
        End If
    End If
    
    Me.Filter = strSQL
    Me.FilterOn = True
    Me.sfrmCostSearch.Form.Filter = strSQL
    Me.sfrmCostSearch.Form.FilterOn = True
    
    Me.txtIndex = strSQL
    Debug.Print strSQL
    
    End Sub

    Any help is appreciated, thanks.

    Friday, September 1, 2017 7:10 PM

Answers

  • Hi,

    If Case is equal to 7, I think you'll need to branch out and construct the filter separately rather than simply assigning a string to strList because, as you have found out, it's syntactically wrong.

    So, before applying the filter, check if "Search All" was selected and build the filter one field at a time, so it will look something like:

    strSQL = "Cost" & strOp & Me.txtAmount1 & " OR SubTotalCost" & strOp & Me.txtAmount1 & and so on...
    

    Hope it helps...

    • Marked as answer by R'C Friday, September 1, 2017 7:56 PM
    Friday, September 1, 2017 7:24 PM

All replies

  • Hi,

    If Case is equal to 7, I think you'll need to branch out and construct the filter separately rather than simply assigning a string to strList because, as you have found out, it's syntactically wrong.

    So, before applying the filter, check if "Search All" was selected and build the filter one field at a time, so it will look something like:

    strSQL = "Cost" & strOp & Me.txtAmount1 & " OR SubTotalCost" & strOp & Me.txtAmount1 & and so on...
    

    Hope it helps...

    • Marked as answer by R'C Friday, September 1, 2017 7:56 PM
    Friday, September 1, 2017 7:24 PM
  • It did!  I'm not sure about checking "Search All" but what you said made me realize I needed to make 2 If statements to decide which path to choose--just a single field or all of the fields (that are listed, that is).  I'm not sure it's the best solution but it seems to be working.  I tacked on the extra If and moved a couple lines so it reads:

    If Not IsNull(Me.txtAmount1) Then
        If Me.frmFields.Value = 7 Then
            strSQL = "[Cost] " & StrOp & txtAmount1 & " OR [SubTotalCost] " & StrOp & txtAmount1 & " OR [DesignerFeeCost] " & StrOp & txtAmount1 & " OR [ProjectManagementFeeCost] " _
            & StrOp & txtAmount1 & " OR [TotalFeeCost] " & StrOp & txtAmount1 & " OR [TotalCost] " & StrOp & txtAmount1
        Else
            strSQL = strList & StrOp & Me.txtAmount1
    End If

    Thanks DB!

    Friday, September 1, 2017 7:56 PM
  • Hi,

    Congratulations! Glad to hear you got it sorted out. Cheers!

    Friday, September 1, 2017 7:58 PM