Change query parameters with form

[meierswa]

I am trying to build a form that will allow the user to alter the criteria of a query and then print a corresponding report.

I have two text boxes on the form with the following criteria in one QBE panel - Between [Forms].[frmActivity].[start date] And [Forms].[frmActivity].[end date]

This allows the user to specify the dates for the report.

I then tried to add a list box to the form and used the following critieria on the query -Like [Forms].[frmActivity].[processor list]

Here's the problem. It works if the Multi Select is set to none but that only allows me to choose one processor. If I set the Multi Select to extended, my report comes back blank.

Any suggestions

 

[RickSpr]

The expression "[Forms]![frmActivity]![processor list]" (note the exclamation points, not periods) obtains the Value property of the "processor list" control.

For a non-Multiselect list box, the Value property is the selected item, so your criteria comes out as Like 'item1' when item1 is selected. That will work fine.

For a Multiselect list box, though, the Value property is always Null, so your criteria comes out as something like Like Null, which never matches anything.

You can't use Like to match a list of items; you need to use In instead. What you would like to do is change the criteria to come out as In ('a','b','c'...) where a, b, c, etc. are the text of the selected items.

Unfortunately, you can't do that by referring to the form in a criteria VBA expression. The In operator is unique in that it takes a list of strings as its argument, and "list of strings" is not a data type that VBA understands. You could use VBA expressions for the individual items in the list, but then you'd have to require that number of items to be selected each time, which would be no good for this application.

The only way I can think of to get this to work using a query, is to set your criteria to a user-defined function that takes the value of the Processor column for the current row, and returns whether a match is selected in the list box. Add this column to the QBE grid:

          Field:    Dummy1: True
          Show:     not checked
          Criteria: IsSelected([Processor])

where "Processor" is the name of the column your Like criteria was applied to.

Then create this function:

    Public Function IsSelected(Value) As Boolean
        Dim v As Variant, lst As ListBox
        
        Set lst = Forms!frmActivity![processor list]
**      If lst.ItemsSelected.Count = 0 Then
**          IsSelected = True
**          Exit Function
**      End If
        For Each v In lst.ItemsSelected
            If Value = lst.ItemData(v) Then
                IsSelected = True
                Exit Function
            End If
        Next v
        IsSelected = False
    End Function

(The lines marked "**" provide that, if no items are selected in the list box, it's as if all of the items are selected. If you omit them, and no items are selected, the report will come out blank.)

This does work (I tried it), but it has a big disadvantage: The query must call this function for each row in its entire input recordset. The function has to find the list box and scan through the selected items to see if there's a match for the Processor column. Some of this--especially finding the list box--is relatively slow, and since it has to be done for each record (including the ones that are not wanted on the report), it could really cause your query to run slow. It will be even worse if you want two or more Multiselect list boxes to match on.

It does work, though, so if your query source isn't too large it might perform acceptably.

As I said, this is the only way I can see it working using a query. A better way, certainly a faster one, would be to have the form construct a criteria string itself (in code) from its own control values. For Multiselect list boxes, it would create a fragment like "[Field] In ('a','b','c'...)". For multiple list boxes, it would combine the fragments with AND operators. When it opens the report, it simply specifies the constructed criteria string as the "where condition" argument to OpenForm. The query wouldn't need any criteria in this case. (Technically, you wouldn't even need the query any more.)

If you don't already know how to use code in Access applications, this might be a good time to learn. Rick Sprague