none
Using VBA function for In statement in query not working RRS feed

  • Question

  • Hi.

    I have a list box on a form where the user can select more than 1 item from the list before clicking a button to open a query.  I am using VBA to build the criteria list and calling it from the query.

    The query isn't returning any results though.  If I enter the string created by the VBA into the query it works, and also shows the result if I use the function as a field within the query.  It's a bit frustrating as I cannot see what is wrong.

    Can anyone see what I am doing wrong?

    Function:

    Public Function Get_Selected_Distribution_lists_v2() As String

        ' Loop through the selections in the Distribution list box
        For Each varItem In [Forms]![frm_Front_Page]!lst_Distribution_Lists.ItemsSelected
           strCriteria = strCriteria & Chr(34) & Trim([Forms]![frm_Front_Page]!lst_Distribution_Lists.ItemData(varItem)) & Chr(34) & ","
        Next varItem
        
        Get_Selected_Distribution_lists_v2 = strCriteria ' (Returns "Distribution List A","Distribution List B")
         
    End Function

    SQL: 

    SELECT tbl_Distribution_Lists.[Contact Group Name], tbl_Distribution_Lists.SSC, tbl_Distribution_Lists.[Customer Name], tbl_Distribution_Lists.[Market Segment], tbl_Distribution_Lists.Member, tbl_Distribution_Lists.[Member Email Address]
    FROM tbl_Distribution_Lists
    WHERE (((tbl_Distribution_Lists.[Contact Group Name]) In (Get_Selected_Distribution_lists_v2())))
    ORDER BY tbl_Distribution_Lists.[Contact Group Name];

    Monday, October 8, 2018 10:01 AM

Answers

  • The SQL engine expects a fixed list; it doesn't evaluate expressions in the In (…) clause.

    You could use the following function:

    Public Function IsSelected(s) As Boolean
        Dim varItem
        If Not IsNull(s) Then
            For Each varItem In [Forms]![frm_Front_Page]!lst_Distribution_Lists.ItemsSelected
                If Trim([Forms]![frm_Front_Page]!lst_Distribution_Lists.ItemData(varItem)) = s Then
                    IsSelected = True
                    Exit For
                End If
            Next varItem
        End If
    End Function

    and change the WHERE-clause to

    WHERE IsSelected(tbl_Distribution_Lists.[Contact Group Name]) = True


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

    Monday, October 8, 2018 11:18 AM

All replies

  • The SQL engine expects a fixed list; it doesn't evaluate expressions in the In (…) clause.

    You could use the following function:

    Public Function IsSelected(s) As Boolean
        Dim varItem
        If Not IsNull(s) Then
            For Each varItem In [Forms]![frm_Front_Page]!lst_Distribution_Lists.ItemsSelected
                If Trim([Forms]![frm_Front_Page]!lst_Distribution_Lists.ItemData(varItem)) = s Then
                    IsSelected = True
                    Exit For
                End If
            Next varItem
        End If
    End Function

    and change the WHERE-clause to

    WHERE IsSelected(tbl_Distribution_Lists.[Contact Group Name]) = True


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

    Monday, October 8, 2018 11:18 AM
  • That's great thanks :) 


    Monday, October 8, 2018 11:42 AM