Query criteria from function giving problems...

[itchyII]

Hello all,
I have an interesting problem. I am running Access 2003. I have a form that has a listbox. The listbox contains a set list of values (department numbers) and the Multi Select option is set to “Extended”. The purpose of the list box is so that the user can select one or many of the departments and then print a report based on this information. I have a function that I wrote to go through the list box selections and create a list and I have placed a reference to the function in the criteria row of a query.

Here is the function code:

Function returnSelectedDepartments() As String
Dim list As String

For Each itm In Forms!frmMain!DeptList.ItemsSelected
    If Forms!frmMain!DeptList.Column(0, itm) = "All" Then
        list = "30 OR 31 OR 32 OR 33 OR 35 OR 36 OR 37 OR 38 OR 39 OR 40 OR 41 OR 42"
        'also tried this --  list = "'30' OR '31' OR '32' OR '33' OR '35' OR '36' OR '37' OR '38' OR '39' OR '40' OR '41' OR '42'"
    Else
        If list = "" Then
            list = list & Forms!frmMain!DeptList.Column(0, itm)
            'also tried this --  list = list & "'" & Forms!frmMain!DeptList.Column(0, itm) & "'"
        Else
            list = list & " OR " & Forms!frmMain!DeptList.Column(0, itm)
            'also tried this --  list = list & " OR " & "'" & Forms!frmMain!DeptList.Column(0, itm) & "'"
        End If
    End If
Next itm

returnSelectedDepartments = list

End Function

The problem is, that if the user selects only one department, this works fine. If the user selects multiple departments, the query returns no records. I think that it might have something to do with the fact that the field I am trying to filter on in the query is set as a text field, but really contains numeric like values (30,31,32, etc). I am passing a string to the criteria row in the query but I think Access is adding quotes somewhere and it is screwing everything up. I tried passing the string with the single quotes already included, but it still doesn’t work. I even tried outputting the function to text box on the form and then tried referencing the text box in the criteria of the query, instead of the function. This gave me the same problem. But if I copy the text from the text box and paste it into the criteria row of the query, the query works fine. Has anyone experienced this problem?

Thanks,
ItchyII

 

[PHV]

Parameters in query are for values only, ie no operator (like Or) is allowed.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[itchyII]

Hi PHV, you say that it is not allowed, but if I enter the same text manually, it works just fine. I prefer to use 'Or' rather than creating an individual line for each parameter. Just as a note, I also tried using 'In(blah,blah)' which also worked when I typed it in the query, but not when I tried passing it dynamically.

ItchyII

 

[PHV]

When you type directly in the query window you don't play with parameters.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[FancyPrairie]

This FAQ faq181-5497 will build the Where clause for you. It works for text boxes, single select and multi-select list boxes, combo boxes, ranges, option groups, and check boxes. All you have to do to make it work is:

1. Create a new module and copy and paste the functions from the FAQ.
2. Set the tag property of you list box (and/or other controls) as specified in the FAQ.
3. Open your report as specified in the FAQ

 

[itchyII]

Hello FancyPrairie, thanks for the response. However, I'm not sure that this solution will work in my case because the reports that I will be running are not bound to any queries or tables. They all have graphs on them that are bound to queries. The query that I am trying to filter is the first in a series of queries that are required for the graphs to work properly. Any other suggestions?

ItchyII