MS Query with various multiple value parameters where there can be an empty parameter

Question

I have 5 multiple select listboxes in Excel. The selected content of every listbox is each written in one cell, separated with a comma.

For example in cell A1 all the selected names: Tim, Miranda, Laura

Those cells are the criteria for an Access query (where clause).

I open MS Query and add the following where clause to the query and define the parameters:

Where (Instr(?, [name])>0 And Instr(?, [number])>0 And Instr(?, [city])>0 And Instr(?, [phone])>0 And Instr(?, [email])>0) 

It works quite well, however if one of the parameter fields is empty (for example the user didn't select any city) the query returns all lines, where city is empty instead of ignoring that clause in this case.

How do I solve this? Perhaps there is another solution using VBA and dynamic SQL.

Note: I have to use Excel for the listboxes instead of Access formulas because the tool shall also be used by persons who do not have access to the database.

Answer 1

From my experience, it may be returning null in some cases and affecting your comparison in the clause.

what happens when you try the len function:

    Where Instr(?, [name])>0 And Instr(?, [number])>0 And (Instr(?, [number])>0 and 
NOT ISNULL([CITY])) And Instr(?, [phone])>0 And Instr(?, [email])>0)**

See the code change above. Sorry, saw your update, I just need to get a grip on the problem. Do you want all records including those that have "empty" cities? But I thought you wanted it to skip those? Another way I search for this is - NOT ISNULL([CITY]) . I work in an environment with a LOT of Access databases and they are quirky!

Answer 2

I solved the Problem by using dynamic SQL in VBA. If nothing is selected, I link all rows in the listbox to a string. This is an example for one listbox which contains names (the code does not contain the necessary connection to the database):

Dim string1 As String
Dim rngParams As Range, rngCell As Range
Dim i As Long

'String name
  With Worksheets("Table1")
    For i = 0 to .ListBox1.ListCount - 1
        If .ListBox1.Selected(i) Then
        If string1 = "" Then string1 = "("
            string1 = string1 & "'" & .ListBox1.List(i) & "',"
        End If

    Next i
End With

If string1 <> "" Then
    string1 = Left(string1, Len(string1) - 1)
    string1 = string1 & ")"
End If

'If nothing is selected, use all names and write them into one string
 If string1 = "" Then
    string1 = "("
    With Worksheets("table2")
        Set rngParams = .Range("allNames")
        For Each rngCell In rngParams.Cells
            string1 = string1 & "'" & rngCell.Value & "',"
        Next rngCell
        string1 = Left(string1, Len(string1) - 1)
        string1 = string1 & ")"
    End With
 End If

strSQL = "SELECT Name FROM accesstable WHERE Name IN " & string1 

Answer 3

I would use the LIKE operator. In Excel populate other cells and point the parameters to them

=if(<cityCell> ="","_%",<cityCell>&"%")

and change the query to

Where [name] like ? And [number] like ? And[CITY] like and [phone] like ? And [email]like ?

In this way you can handle other transgressions by users i.e. change the Excel formula to

=if(<cityCell> ="","_%",PROPER(<cityCell>)&"%") OR

So user entry of toronto will look for Toronto. I use this a lot with UPPER as lots of databases contain uppercase entries.. i.e. Canadian postal codes