Resurrection of a search form added functionality

[west]

About a year ago I created a search form based on Allen Brownes Search model. Working fine, but the user wanted added functionality, see here, after a few tries the users didn’t wanted the option anymore because they were in a hurry to deploy. But now their management is asking again for a wider scope in that state search. I made an example model. It works fine, but only if I always have some value in the [State C] option, if left blank it won’t work, a Run-time error 3075, will pop up.


I have tried many combinations of parenthesis, but, haven’t got any luck.
Thanks for the time.

 

[JHB]

Try it now:

 

[jdraw]

Seems JHB had similar adjustment as I did.

Here is the code change I found along with some Debugs

    '----------Deal with State------------
    ' fix the quotes
    If Not IsNull(Me.cboStateA) Then
        strWhere = strWhere & "([State] = '" & Me.cboStateA & "') Or "
        'Debug.Print "StateA " & vbTab & strWhere
    End If

    If Not IsNull(Me.cboStateB) Then
         strWhere = strWhere & "([State] = '" & Me.cboStateB & "') Or "
        'Debug.Print "StateB " & vbTab & strWhere
    End If

      If Not IsNull(Me.cboStateC) Then
           strWhere = strWhere & "([State] = '" & Me.cboStateC & "') AND "
          'Debug.Print "StateC " & vbTab & strWhere
      End If

    lngLen = Len(strWhere) - 4  'was removing ")"
    If lngLen <= 0 Then
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else
        strWhere = Left$(strWhere, lngLen)
        'Debug.Print "strwhere going to filter   " & strWhere
        Me.Filter = strWhere
        Me.FilterOn = True
    End If

 

[west]

Awesome! you guys nail it. I would never have found the solution. My focus was on the parenthesis I placed them everywhere arround that group.

Thank you very much!!!

-JC

 

[jdraw]

We are happy to help.
Good luck with your project.

 

[west]

Guys, sorry to abuse the goodness of your heart again, but, I tried adding a [Names] group too, following the pattern on the [State] group corrections you made, but I can't seem to find the correct combination. I'm having a "Run-time error 3075" in that particular group. The original State group still works fine by the way, the new additions didn't break it.

The lab DB is attached,

Thanks for the support.

-JC

 

[jdraw]

Try this change in your code

 '-----------Names Check-----------
    
    If Not IsNull(Me.cboNameA) Then
        strWhere = strWhere & "([Names] ='" & Me.cboNameA & "') OR "
        'Debug.Print "1  " & strWhere
    End If
    
    If Not IsNull(Me.cboNameB) Then
        strWhere = strWhere & "([Names] = '" & Me.cboNameB & "') OR "
        'Debug.Print "2  " & strWhere
    End If
    
    If Not IsNull(Me.cboNameC) Then
        strWhere = strWhere & "([Names] ='" & Me.cboNameC & "') AND "
        'Debug.Print "3  " & strWhere
    End If
    
    '----------------------

 

[west]

Look at what the power of meticulous observation can do. I was so sure I had done everything correctly.

.... Thank you jdraw, if it weren't for you I'd still be chasing my tail.

For other reader I had left out a few "=" sings

-JC

 

[jdraw]

If you set up some Debug.Print statements while developing, you can quickly see the issues.
Good luck with your project.