How to create compund filters on a form

Create a GO button, and in that button's OnClick event write SQL code that goes something like this:

Private Sub go_click()

Dim sSQL as string, sWhere as string

sSQL = "SELECT blah, blah, blah FROM YourTable"

If Nz(Me.Combo1.Value) <> '' then
   sWhere = sWhere = "column1 = " & Me.Combo1.Value & " AND"
End IF

If Nz(Me.Combo2.Value) <> '' then
   sWhere = sWhere = "column2 = " & Me.Combo2.Value & " AND"
End IF

If Nz(Me.Combo3.Value) <> '' then
   sWhere = sWhere = "column3 = " & Me.Combo3.Value & " AND"
End IF

If Nz(Me.Combo4.Value) <> '' then
   sWhere = sWhere = "column4 = " & Me.Combo4.Value & " AND"
End IF

If Len(sWhere) > 0
  sWhere = "WHERE " & Left(sWhere, Len(sWhere) - 4)
end if

Me.RecordSource = sSQL & sWhere
Me.Requery

End Sub

Enjoy.
Jim

So here is the Syntax that I applied to my specific form.  I can not compile this.


Private Sub Command46_Click()
Dim sSQL As String, sWhere As String

sSQL = "SELECT * FROM tblProviders"

If Nz(Me.Combo44.Value) <> '' then
   sWhere = sWhere = "column1 = " & Me.Combo44.Value & " AND"
End If

If Nz(Me.Combo40.Value) <> '' then
   sWhere = sWhere = "column2 = " & Me.Combo40.Value & " AND"
End If

If Nz(Me.Combo42.Value) <> '' then
   sWhere = sWhere = "column3 = " & Me.Combo42.Value & " AND"
End If

'If Nz(Me.Combo4.Value) <> '' then
'   sWhere = sWhere = "column4 = " & Me.Combo4.Value & " AND"
'End If

If Len(sWhere) > 0
  sWhere = "WHERE " & Left(sWhere, Len(sWhere) - 4)
End If

Me.RecordSource = sSQL & sWhere
Me.Requery


End Sub

>sSQL = "SELECT * FROM tblProviders"
add a space before the right double-quote.  My bad.   Should be..

sSQL = "SELECT * FROM tblProviders "

Also, you'll need to delineate what your feeding into the parameters, like this...

'Number - use single quotes
sWhere = sWhere = "column2 = '" & Me.Combo40.Value & "' AND"

'Dates - Use pound signs
sWhere = sWhere = "column2 = #" & Me.Combo40.Value & "# AND"

>'If Nz(Me.Combo4.Value) <> '' then
Better to name your controls to what they represent, such as cbo_customer_number, cbo_invoice_date, cbo_state, etc.  I'm not likely to remember what Combo4 means.

Also for kicks and giggles, until we have this down ...

End If

msgbox sSQL & sWhere   '<--  add this line, and look at it when it runs.

Me.RecordSource = sSQL & sWhere
Me.Requery

Getting closer

When running the procedure i get a compile error no the If Len(...


Dim sSQL As String, sWhere As String

sSQL = "SELECT * FROM Providers "

If Nz(Me.Cbo_city.Value) <> "" Then
   sWhere = "column1 = '" & Me.Cbo_city.Value & " AND"
End If


If Nz(Me.Cbo_county.Value) <> "" Then
   sWhere = sWhere = "column2 = " & Me.Cbo_county.Value & " AND"
End If

If Nz(Me.Cbo_State.Value) <> "" Then
   sWhere = sWhere = "column3 = "" & Me.Cbo_State.Value & " And ""
End If

'If Nz(Me.Combo4.Value) <> '' then
'   sWhere = sWhere = "column4 = " & Me.Combo4.Value & " AND"
'End If

If Len(sWhere) > 0
  sWhere = "WHERE " & Left(sWhere, Len(sWhere) - 3)
End If

MsgBox sSQL & sWhere   '<--  add this line, and look at it when it runs
Me.RecordSource = sSQL & sWhere
Me.Requery

>sWhere = sWhere = "column2 = " & Me.Cbo_county.Value & " AND"
I have one two many equals sign in these lines.  My bad.  Change the above lines to

sWhere = sWhere  & "column2 = " & Me.Cbo_county.Value & " AND"
                               ^^^

Where you have Column1, Colum2, Column3, Column4, change those to the column names in the table that you're trying to query.

>   sWhere = sWhere = "column3 = "" & Me.Cbo_State.Value & " And "
After the AND there should only be the double quote.
The idea is to have a sinqle-qoote around the passed Me.cbo_state.Value.

>sWhere = sWhere = "column2 = " & Me.Cbo_county.Value & " AND"
Keep the space to the right of the AND, as it will cause an error if it runs on to the next block.

>  sWhere = "WHERE " & Left(sWhere, Len(sWhere) - 3)
Change the 3 to a 4, so it removes the final AND and the space.

<sorry for so many iterations here>

Trust me, once you get the hang of this it'll be easy..

I thought it strange that you weren't using a continuous form to show your filtered choices so I revised your database a bit.  I also made use of the form's filter property when selecting your combo boxes.

One suggestion.  You can use the State abbreviation as the primary key instead of the 2 other fields that you have with numeric IDs.
Filters.accdb

Thanks IrogSintra


A quick Question, on the combo boxs, if they decide to not utilize one when filtering, is there a way to clear the value in the combo?

Just curious. Which route did you select to clear out a value in a combo box?

i really havent been able to get this to work correctly.  I added a clear button next to each combo box.

Private Sub Command53_Click()
Me.cmbState.Value = Null
Forms!frmProvider.frmProvider_sf.Requery
End Sub

However this does not requery the entire form, so the form is still displaying the results of the filter!   it does clear the value from the combo box.    Also the form you provided is confusing me, when I tab through the various fields the doesnt scroll horizonally, so you cant see all the fields.  The other thing that confuses me is the layout of the form, I am trying to rearrange the fields , I wanted Salutation to be the first field and I can't figure out how to move the fields around.

Thank you

I'd like to suggest that you take time to give descriptive names to your controls.  This will help you in the long run when you revisit your code.  If you see a routine called btnState_Click, you'd know right away what this procedure pertains to.  Command53_Click, on the other hand, doesn't give much help.
    That aside, here's how your clear process should be:
Me.cmbState.Value = Null
Call FilterProviders
   As for the layout of the form, when you are in design mode, just drag the fields around and put them in the order that you want.  You can drag around a group to select them all and move them as one.
    Let me look into the horizontal scrolling issue.

Okay, so apparently having it on a subform doesn't work too well if the form is wider than the screen when it comes to automatic horizontal scrolling.  So here is your db without the subform.

Also, in moving controls around in the design mode, you can hold down the Ctrl key and click on the controls that you want to move as a group.
Filters--1-.accdb

That is awesome works fine!   Now I know you are going to kill me.   This form will be used by the user to select a doctor and link it to a claim that is entered in the system.  Is there a way to make a selection of one of the records and then populate on another form?

You should really open up another thread since this question is unrelated to your original post.

Will do thx for all Ur help

I just noticed an error with the db I uploaded.  You need to make a correction.  Make your combo box cmbState Unbound and delete the form's On Load event.

Here's the corrected version.  Sorry about that.
Filters--1-.accdb