?
Solved

How to create compund filters on a form

Posted on 2012-08-14
21
Medium Priority
?
338 Views
Last Modified: 2012-08-18
in the attached Sample database, I have a form "frmProvider".  I would like to give the user the ability to filter on one or more criteria and then have the form only show the records that meet that criteria.

i created 4 unbound combo boxes and can not figure out a way if the user selects more than one combo box to filter on each criteria selected.


Also on the state filter combo box its not working correctly either.

i have attached the sample database if this helps.
Filters.accdb
0
Comment
Question by:seamus9909
  • 8
  • 7
  • 6
21 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38293412
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
0
 

Author Comment

by:seamus9909
ID: 38293533
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
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38293641
>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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 66

Expert Comment

by:Jim Horn
ID: 38293645
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
0
 

Author Comment

by:seamus9909
ID: 38293727
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
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38293753
>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"
                               ^^^
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38293796
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.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38293798
<sorry for so many iterations here>
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38293802
Trust me, once you get the hang of this it'll be easy..
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38294777
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
0
 

Author Comment

by:seamus9909
ID: 38297079
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?
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 2000 total points
ID: 38297216
Right now it works if you clear out the selection in a combo box and either press either Tab or Enter.  This of course is inconvenient.  You have a few choices.  
Add another choice to each combo box saying something like ANY COUNTY or ANY SPECIALITY.
Add a clear button  that clears all combo boxes.
Add a small clear button beside each combo box to clear each one individually.
Add a double click event to the label of each combo box that clears it (if you go this route you would need to have a note somewhere on your form instructing the user about this since it's not obvious).
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38306678
Just curious. Which route did you select to clear out a value in a combo box?
0
 

Author Comment

by:seamus9909
ID: 38307785
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
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38307963
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.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38308070
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
0
 

Author Comment

by:seamus9909
ID: 38308322
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?
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38308344
You should really open up another thread since this question is unrelated to your original post.
0
 

Author Comment

by:seamus9909
ID: 38308513
Will do thx for all Ur help
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38308690
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.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38308697
Here's the corrected version.  Sorry about that.
Filters--1-.accdb
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question