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

[Andrew Hallett]

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:

http://www.blueclaw-db.com/multi_select_parameter_form.htm

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.

[Steven Kogan]

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:

Private Sub Run_Query_Click()
Dim x As Long
'
' clear out old values
'
docmd.setwarnings false
DoCmd.RunSQL ("Delete * from TempTable")
'
For x = 0 To Me.Parameter_List.ListCount - 1
    If Me.Parameter_List.Selected(x) = True Then
        DoCmd.RunSQL ("Insert into TempTable (Parameter) values ('" & _
            Me.Parameter_List.ItemData(x) & "')")
    End If
Next x
docmd.setwarnings true
'
' open query
'
DoCmd.OpenQuery "MyQuery"
End Sub

[NeoPa]

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 :

WHERE (([State] In(SELECT [State]
                   FROM   [T_States]))
   OR  (DCount('*','[T_States]','[State]=''' & [State] & '''')=0))

Welcome to Bytes!

[hype261]

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.