Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

Multiple combo boxes filtering query

Hi,

I have four combo boxes:
cmbOrg
cmbStaff
cmbData
cmbIssue

The user can select a value from each of them - or some of them or none of them, they are not 'enforced'.

I want them to filter a query so if the user selects a value from cmbOrg then that would filter the query on the value selected.  This should be the same for all.  However if the user does not select a value, how do I get the query to return ALL the results?
I.E.
cmbOrg = 1
cmbStaff = nothing selected
cmbData = nothing selected
cmbIssue = 4
This would make the query return results for Org = 1 and Issue=4.  But if I change it to be :

cmbOrg = nothing selected
cmbStaff = nothing selected
cmbData = nothing selected
cmbIssue = 4

This would make the query return results EVERY ORG with Issue=4.  

I hope I explained this ok - its a bit confusing.  I just need help figuring out the syntax in the filter.  My query is this:

Select org, staff, data, issue from table1;

Thanks!!!
0
jetera
Asked:
jetera
  • 6
  • 6
  • 4
  • +2
3 Solutions
 
ComputerAidNZCommented:
Can you post your DB or, if it is large, a subset of your DB that has the necessary bits to determine your request?  It may just be a small change on the form or something here!!
Thanks
0
 
Dale FyeCommented:
If these are not cascading, I generally have a subroutine that I call from the AfterUpdate event of each of these, or use the Click event of a command button.  The latter method is more efficient if you have a large number of records and don't want to be able to make multiple changes to the combo boxes without having to wait for a filter to be applied between each change.

Private Sub FilterForm()

    Dim strCriteria as String

    if Len(me.cmbOrg & "") <> 0 Then strCriteria = "[ORG] = " & me.cmbOrg

    if Len(me.cmbStaff & "") <> 0 then
        strCriteria = strCriteria & iif(Len(strCriteria) > 0, " AND ", "") & "[Staff] =" & me.cmbStaff
    end if

    if Len(me.cmbData & "") <> 0 then
        strCriteria = strCriteria & iif(Len(strCriteria) > 0, " AND ", "") & "[Data] =" & me.cmbData
    end if

    if Len(me.cmbIssue & "") <> 0 then
        strCriteria = strCriteria & iif(Len(strCriteria) > 0, " AND ", "") & "[Issue] =" & me.cmbIssue
    end if

    me.Filter = strCriteria
    me.FilterOn = Len(strCriteria) > 0

End Sub
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jeffrey CoachmanCommented:
jetera,

This is why I generally don't like the "Select All if nothing is entered" logic.

If you want *all selected*, then have a choice in the combobox that is literally:
 *Select All*

Selecting All when nothing is entered is like saying that if you don't place an order in a restaurant, then you want *All* items from the menu by default.

If you open outlook and you don't select an email recipient, does it select all of them by default?
No

If you want Soda, Select "Soda"

If you want Butter, Select "Butter"

If you want All seasonings and Filet Mignon, select the "All Seasonings" selection from the Seasonings combobox and "Filet Mignon" from the Meat combobox.

If you want Gone with the wind, Select "Gone with the wind"

If you want GoofyGoblets, then please select "GoofyGoblets" form the list

If you want nothing, then select the "Nothing" item from the list

If you want ALL select the "ALL" item form the list

Make sense?

This is a bit trickier to do (Public variables, ..etc), but again, IMHO, it makes the interface more intuitive and makes the user selections a lot more logical (when selecting them and viewing them)...

Sample attached.

;-)

JeffCoachman


Access-EEQ27020357Select-ALL-Ite.mdb
0
 
ComputerAidNZCommented:
Some very valis points here!! BOAQ2000 is right in the sense that a selection is best entered, else the end user doesnt k now what he is getting, but in all fairness, depending on who is using the DB and for what purpose, it may be si,mplere to have everything in a list.
If you can be more specific as to your requirements, we can direct you along the right path, instead of giving our opinions which may or may not be wanted anyway!!
Any DB posted yet?
0
 
jeteraAuthor Commented:
I can't post my DB - not allowed, sorry.

But I am working through the suggestions and I do welcome all the comments/opinions - I am constantly trying to improve so I like to have them!

Thanks
0
 
ComputerAidNZCommented:
Maybe you could give us some clues as to whether this is on a form, in a query or report.
If it is a query you can post the SQL for our perusal.  A table can be a a jpeg file of the columns and a bit of data.  A form can be a jpeg and maybe the code behind it that is relevant.
0
 
Jeffrey CoachmanCommented:
@ ComputerAidNZ

LOL!
As you can see this is one of my pet peeves.
;-)

...and I was in no way suggesting that any of the previous experts suggestions were lacking.
(as they correctly addressed the askers Question, as it was posted)

But, yes, I realize that sometimes something simple like this may be OK.
(just put a note somewhere on the form that says:
   "Leaving this box blank will find all____.")

And yes, in all fairness you can even search here and find many posts on mine with the "select All if left blank" solution.
...as this was a fascinating topic for me at that time...

;-)

Jeff
0
 
ComputerAidNZCommented:
@ BOAQ2000

I am quite new to this forum and find it extemely fascinating seeing what people write, both in request and in suggestion.
I came from a background that makes the assumption that ABSOLUTELY ANY USER can find their way around the DB (don't forget, this is Access, not SQL, so we're talking of Homies and wannabees here), because most people do not undestand the concept of DB's and relationships and referential integrity and.... so it is the responsibility of the designer to ensure the end user can operate without messing up.  SQL guys come from a background that has professional code in teh background - no end user is EVER going to see the complexity that goes into SQL, plus it is far more robust, and has better, well... everything, so their viewpoint is at a higher level.

Access, well this is a toy people like me cut their teeth on such stuff!!

After saying that: there is ALWAYS a better way of doing something, we just don't know it yet!!  And whilst I agree that in an ideal world your code suggestions are spot on, with checks and selections being specified correctly, I IMHO, think Access is not the ideal world!!  (Said in fear of being shot down by Access Worshippers).

Now, it sounds like you've been on this forum a while, so I suppose you have seen more, heard more and settled more Q's and A's than I can even imagine, so I take my hat off to you.  I just like to add a little flavour (US flavor) to the array of answers, which BTW, I think forums (I am not saying this one, since I have not been here long enough) tend to try to give opinions rather than aswer the question, everyone is different, but if the questioner wanted an opinion, they would ask (not always true, I know).

At least this forum has a lot more REAL knowledgable people rather than a few forums I've seen that people give answers that are TRULY wrong, just because they WANT to be helpful.  My opinion - If you don't know; don't tell!!

Well, keep posting, as I get good value out of your answers - it's not just the questioner that benefits from the info given.  I love to see the many ways people answer queries - it just goes to prove there are MANY right answers (PS: How many times have you given a GREAT answer only to have some weird answer get all the points?? - that sucks, eh!)
James
0
 
ComputerAidNZCommented:
@ BOAQ2000

Hey, just read your profile, briefly.
7 friggin'  million points, crap, that's more brownie points than Obama and Osama put together!!

I might shut up next time I make a comment - and read their profile FIRST!!!

MHO doesn't quite stack up to 7M, eh!
At least I know where to get answers to my Access issues - no doubt Microsoft will provide me with plenty of headaches.
::-)
0
 
ComputerAidNZCommented:
Back to JETERA, what stage are you up to?
0
 
Jeffrey CoachmanCommented:
That is the beauty of this site.

A member can get a few different points of view, and decide for themselves which is best.

As far as my points go, ...there are plenty of experts with more.
And there are plenty of experts with half my points but 10 times my experience in Access.

;-)

Jeff
0
 
jeteraAuthor Commented:
Sorry- I am still working on Capricorn1's idea.  It is a form with four combo boxes, they do not rely on each other, anything can be picked from any of them.  Then a button creates the filter and opens the query.

Jetera
0
 
jeteraAuthor Commented:
Capricorn1 - at the bottom part of the code - how do I change that to reflect the filter of a query called QMultipleSearch?

----
If Len(sFilter & "") > 0 Then
    Me.Main.Form.Filter = sFilter
    Me.Main.Form.FilterOn = True
    Else
    Me.Main.Form.Filter = "[challenges] like '*'"
    Me.Main.Form.FilterOn = True
End If
   
--------
0
 
Rey Obrero (Capricorn1)Commented:
post the sql statement of the query  "QMultipleSearch"
0
 
jeteraAuthor Commented:
sql = " SELECT CCRS_ORGANIZATION.ORGANIZATION_CODE, CCRS_ORGANIZATION.ORGANIZATION_DESC, Track.OpenDate, Track.CategoryType, USER_PROFILE.LAST_NAME, USER_PROFILE.FIRST_NAME, USER_PROFILE.EMAIL, Track.HCRS_CCRS, Track.AssignedTo, Staff.StaffName, Track.CorrespondenceType, Track.IssueDetail, Track.Redirect, Track.RedirectedTo" & _
            " FROM (((CCRS_ORGANIZATION RIGHT JOIN Track ON CCRS_ORGANIZATION.ORGANIZATION_CODE = Track.[Facility Number]) LEFT JOIN zCategory ON Track.CategoryType = zCategory.ID) LEFT JOIN Staff ON Track.AssignedTo = Staff.StaffID) LEFT JOIN USER_PROFILE ON Track.UserID = USER_PROFILE.UserID"


0
 
Rey Obrero (Capricorn1)Commented:

dim oSql as string, qd as dao.querydef, revSql as string

set qd=currentdb.querydefs("QMultipleSearch")

oSql=qd.sql

revsql=oSql & " Where & " sfilter

 'to see if the sql statement created is correct
 'add this line
debug.print revSql

qd.sql=revSql

docmd.openquery "QMultipleSearch"

'restore the original query statement

qd.sql=oSql

0
 
jeteraAuthor Commented:
Hi Capricorn1 - is there a way to trim the original sql string to get rid of the ";" at the end, in the debug window its showing like this:
Select...; where blah blah blah.

Thanks!  So close!
0
 
Rey Obrero (Capricorn1)Commented:
change this

oSql=qd.sql

with

oSql=replace(qd.sql,";","")
0
 
jeteraAuthor Commented:
Works like a charm!!!!!!!!!!!!!!!!!  Thanks!!!!!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 6
  • 6
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now