Criteria search RRS feed

  • Question

  • Please help me with this syntax...................

    Criteria search syntax:

    [Enter a value to search]--- WORKS great when I enter a single value.

    [Enter a value to search]-- does NOT WORK when I enter multiple values separated by OR

    Can any body help me with this?

    Also is there any better ways to search multiple numbers in same field (criteria)

    Thanks in advance!

    Thursday, October 27, 2016 7:04 AM

All replies

  • I would prefer to create a multi-select list box on a form, and create the SQL for the where-condition in VBA code.

    But there is a trick to use a comma-separated list of values as parameter.

    See http://www.pcreview.co.uk/threads/how-to-use-a-parameter-query-with-multiple-values.1179898/ for tips on both.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, October 27, 2016 2:50 PM
  • As Hans says, the best solution is to have a multi-select list box in which any number of items can be selected.  You'll find an example as SearchDemo.zip in my public databases folder at:


    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file uses a simple database of stocks held by stores as an example.  The option to 'Select Criteria for Report' opens a form with a multi-select list box and an option group which determines whether stores which hold all or any of the selected items are returned.  The code to execute the search after selecting items in the list box and either the 'all' or 'any' option is as follows:

        Const REPORTCANCELLED = 2501
        Const MESSAGETEXT = "No matching items to report."
        Dim varItem As Variant
        Dim strStockIDList As String
        Dim strCriteria As String
        Dim ctrl As Control
        Set ctrl = Me.lstStock
        If ctrl.ItemsSelected.Count > 0 Then
            For Each varItem In ctrl.ItemsSelected
                strStockIDList = strStockIDList & "," & ctrl.ItemData(varItem)
            Next varItem
            ' remove leading comma
            strStockIDList = Mid(strStockIDList, 2)
            If optAnyOrAll = 1 Then 'any item stocked
                strCriteria = "StoreID IN(SELECT StoreID " & _
                    "FROM StoreStock " & _
                    "WHERE StockID IN(" & strStockIDList & "))"
            Else                    'all items stocked
                strCriteria = "StoreID IN(SELECT StoreID " & _
                    "FROM StoreStock " & _
                    "WHERE StockID IN(" & strStockIDList & ") " & _
                    "GROUP BY StoreID " & _
                    "HAVING COUNT(*) = " & ctrl.ItemsSelected.Count & ")"
            End If
            On Error Resume Next
            DoCmd.OpenReport "rptStoreStocks", _
                View:=acViewPreview, _
             Select Case Err.Number
                Case 0
                ' no error
                Case REPORTCANCELLED
                ' anticipated error
                MsgBox MESSAGETEXT, vbInformation, "Warning"
                Case Else
                ' unknown Error
                MsgBox Err.Description, vbExclamation, "Error"
            End Select
            MsgBox "No items selected", vbInformation, "Warning"
        End If

    In my case the button opens a report, but could equally well open a form or assign the value of the strCriteria value to the Filter property of a bound form in which the unbound list box is located.

    Another way of restricting a result set by multiple values is to restrict a query as the RecordSource of a form or report.  When restricting a query by an arbitrary set of literal values the IN operator would normally be used in the query, but this does not accept a parameter as its argument, only a set of literal values.  Microsoft has published InParam and GetToken functions to simulate the use of the IN operator with parameters at:


    You'll find illustrations of their use, amongst other methods, in the Multiselect.zip demo in my same OneDrive folder.  Note that the use of these functions is more reliable than the other method at the above link, using the Instr function, as the latter can return specious substring matches.

    Ken Sheridan, Stafford, England

    • Edited by Ken Sheridan Thursday, October 27, 2016 4:23 PM Typo corrected.
    Thursday, October 27, 2016 4:20 PM