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

Multiselect Query Parameters - Allowing All States When No Selection Has Been Made

P: 1
I'm trying to design a query in Access 2007 that will select clinicians based upon a list of desired counties and specialties. The query receives its parameters from a form where the searcher may choose their desired counties and specialties from two multiselect menus. At present, it works wonderfully assuming that selections have been made. However, I am trying to design it such that if no selection is made, the query will show all specialties and/or counties. I found a nifty little demo that includes just such a function here:

They use this simple command in their WHERE statement:

in (select state from t_states) or dlookup("Count(state)","T_States")=0

Where 'state' is a field of the table, 'T_States,' which holds the selected parameters.

This works beautifully in their demo, but I can't get it to work in my query for one, let alone two, multiselect parameters. The query simply returns empty - no error messages. Any ideas?

I know that a subquery would be an alternative option, but I am rather new to their use and am unclear how I should design one to suit my needs here.
Aug 6 '10 #1
Share this Question
Share on Google+
3 Replies

Expert 100+
P: 107
That is quite nifty.

For this to work you need to use VBA to properly populate the table that contains the selected parameters. You should be able to verify that the table contains what you expect to. Once that part works, then you should be able to adapt the where statement to your app.

Generic code to populate the temporary table and run the query is:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Run_Query_Click()
  2. Dim x As Long
  3. '
  4. ' clear out old values
  5. '
  6. docmd.setwarnings false
  7. DoCmd.RunSQL ("Delete * from TempTable")
  8. '
  9. For x = 0 To Me.Parameter_List.ListCount - 1
  10.     If Me.Parameter_List.Selected(x) = True Then
  11.         DoCmd.RunSQL ("Insert into TempTable (Parameter) values ('" & _
  12.             Me.Parameter_List.ItemData(x) & "')")
  13.     End If
  14. Next x
  15. docmd.setwarnings true
  16. '
  17. ' open query
  18. '
  19. DoCmd.OpenQuery "MyQuery"
  20. End Sub
Aug 6 '10 #2

Expert Mod 15k+
P: 31,768
A subquery wouldn't be an alternative Andrew, as the solution you showed includes one as well.

It may be helpful at this stage to post the whole of the related part of the WHERE clause. Your snippet is frustratingly incomplete.

If I assume for now that you are filtering the field [State] from the table [T_States] then we might try an alternative of :
Expand|Select|Wrap|Line Numbers
  1. WHERE (([State] In(SELECT [State]
  2.                    FROM   [T_States]))
  3.    OR  (DCount('*','[T_States]','[State]=''' & [State] & '''')=0))
Welcome to Bytes!
Aug 7 '10 #3

P: 207
Generally in this situation I dynamically build the sql where statement on the fly. When the user clicks the search button I check to see if the combo boxes or text boxes are NULL or Empty. If they aren't then I append to the SQL statement. You have to keep track of how many things have been added to see if you need to add an AND/OR statement, but this has worked out for me in the past.
Aug 7 '10 #4

Post your reply

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