By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,081 Members | 1,885 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,081 IT Pros & Developers. It's quick & easy.

Create query from multiple combo boxes

WyvsEyeView
P: 46
I have a form that has four unbound combo boxes: cboType, cboVersion, cboStatus, cboReview. I want to write a query that basically lets users make selections in as many combo boxes as apply...only one, all four, or anything in between. Here is the query, with the "meat" of it sketched out. I'm not sure how to capture the "or any" designation & can't find instructions in any of my SQL references. I've tried everything I can guess at in the Query Designer! Thanks for suggestions.

Expand|Select|Wrap|Line Numbers
  1. SELECT tblTopics.desc, tblTopics.type, tblTopics.version, tblTopics.status, tblTopics.review, tblTopics.libDoc
  2. FROM tblTopics
  3. WHERE    tblTopics.type  = Forms!frmReportSelection!cboType OR [any type]
  4.    AND tblTopics.version = Forms!frmReportSelection!cboVersion OR [any version]
  5.    AND tblTopics.status  = Forms!frmReportSelection!cboStatus OR [any status]
  6.    AND tblTopics.review  = Forms!frmReportSelection!cboReview OR [any review]
  7. ORDER BY tblTopics.desc;
Aug 29 '08 #1
Share this Question
Share on Google+
9 Replies


nico5038
Expert 2.5K+
P: 3,072
The "basic" approach to this is to use a UNION and a LIKE like:

1) Base the combobox on a UNION query like:
Expand|Select|Wrap|Line Numbers
  1. select "*", "<All>" from tblX
  2. UNION
  3. select code, description from tblX
  4.  
2) Use in the query the LIKE statement like:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "select * FROM tblX WHEREcode like '" & me.comboCode & "'"
  2.  
Getting the idea ?

Nic;o)
Aug 29 '08 #2

NeoPa
Expert Mod 15k+
P: 31,494
Interesting (and fundamental) question.

First let me start by saying that usually this would be done by changing the SQL dynamically within VBA code and only including the parts where there is a selection.

For this question though...

Expand|Select|Wrap|Line Numbers
  1. SELECT [Desc],
  2.        [Type],
  3.        [Version],
  4.        [Status],
  5.        [Review],
  6.        [LibDoc]
  7.  
  8. FROM tblTopics
  9.  
  10. WHERE (((Forms.frmReportSelection.cboType Is Null)
  11.    OR   ([Type]=Forms.frmReportSelection.cboType))
  12.   AND  ((Forms.frmReportSelection.cboVersion Is Null)
  13.    OR   ([Version]=Forms.frmReportSelection.cboVersion))
  14.   AND  ((Forms.frmReportSelection.cboStatus Is Null)
  15.    OR   ([Status]=Forms.frmReportSelection.cboStatus))
  16.   AND  ((Forms.frmReportSelection.cboReview Is Null)
  17.    OR   ([Review]=Forms.frmReportSelection.cboReview)))
  18.  
  19. ORDER BY [Desc]
If nothing is selected for any particular ComboBox then its value will be Null. In this case the other side of the related OR will be irrelevant. Or, to put it another way, it will only check the field against the ComboBox if the ComboBox has a selected value.
Aug 29 '08 #3

NeoPa
Expert Mod 15k+
P: 31,494
My concept assumes the operator will leave the ComboBox unselected (or cleared) when they choose not to use it to filter the results.

Using Nico's idea of populating the ComboBox with a literal entry of "<All>" could also work (in fact more easily).

An example line in the WHERE clause might be :
Expand|Select|Wrap|Line Numbers
  1. AND  (Forms.frmReportSelection.cboStatus In([Status],'<All>'))
to replace :
Expand|Select|Wrap|Line Numbers
  1. AND  ((Forms.frmReportSelection.cboStatus Is Null)
  2.  OR   ([Status]=Forms.frmReportSelection.cboStatus))
Is that making sense?
Aug 29 '08 #4

WyvsEyeView
P: 46
You guys are the best! I already had some code populating the combo boxes, based on a selection in an option group. I was able to work in your suggested code to add the <All> option, and then I was able to write the correct query. Knowing how to do this is going to be hugely helpful for some other things, so I am very grateful. Thanks again!!!
Aug 30 '08 #5

NeoPa
Expert Mod 15k+
P: 31,494
You're very welcome.

It's always most gratifying when a member gets it. Helping people to learn is what makes it so worthwhile. Welcome to Bytes!
Aug 30 '08 #6

WyvsEyeView
P: 46
Well, I got it, but now I need to get it some more :) My intent with this query was to create a list of records and display them onscreen so that users could edit them...for example, create a list of all topics of version 1 and change applicable ones in the list to version 2. Not surprisingly, the list created from this query is non-updateable. I didn't think about that. What would be the best way to turn this list into an editable one? I thought of creating a recordset from the query but am not sure if the resulting recordset would be editable either. In any case, I have not worked with recordsets and am not sure how to proceed. Any advice?
Sep 5 '08 #7

NeoPa
Expert Mod 15k+
P: 31,494
Can you post your SQL.

I can't see why anything we've discussed so far would cause the query not to be updatable :S
Sep 5 '08 #8

WyvsEyeView
P: 46
I found the problem. My SQL had a SELECT DISTINCT statement. I'm not sure why I included the DISTINCT qualifier...I'm sure I had some reason originally but the query results don't seem to be affected by removing it, and the resulting query is updateable. Thank you!
Sep 5 '08 #9

NeoPa
Expert Mod 15k+
P: 31,494
Aagh, the perils of posting a version that's different from the one you're working with ;)

The important thing is that you've resolved the problem anyway. It was a pleasure to have been involved :)
Sep 5 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.