Having big problems with Variables

[Geoff Codd]

Hi there,

I have a form with various multi-select list boxes and I am trying to pass the values to a query

This is the code for my list box:

Private Sub lstOpCo_AfterUpdate()
Dim OpCo_Criteria As String
Dim ctl As Control
Dim Itm As Variant

    ' Build a list of the selections.
    Set ctl = Me.lstOpCo

    For Each Itm In ctl.ItemsSelected
        If Len(OpCo_Criteria) = 0 Then
            OpCo_Criteria = ctl.ItemData(Itm)
        Else
            OpCo_Criteria = OpCo_Criteria & "," & ctl.ItemData(Itm)
        End If
    Next Itm
      
        pOpCo = OpCo_Criteria

This is my code for the global variables

Option Compare Database
Option Explicit

Public pOpCo As String

Public Function ReturnVariableX() As String

    If (pOpCo) = "" Then
    
        ReturnVariableX = "ALL"
    
    Else
    
        ReturnVariableX = "In (""" & pOpCo & """)"

    End If
    
End Function

This is the sql for my query

SELECT [tblRR25_(Third-party)].[Opco Code], IIf(ReturnVariableX()="ALL",[Opco Code] Like "*",ReturnVariableX()) AS Expr1
FROM [tblRR25_(Third-party)]
GROUP BY [tblRR25_(Third-party)].[Opco Code], IIf(ReturnVariableX()="ALL",[Opco Code] Like "*",ReturnVariableX());

The problem I am having is passing the In Function to the query, but I also think I am making things harder for myself than they really should be.

Any ideas comments always appreciated.

Thanks
Geoff

 

[Travis]

SELECT [tblRR25_(Third-party)].[Opco Code], IIf(ReturnVariableX()="ALL",[Opco Code] Like "*",ReturnVariableX()) AS Expr1
FROM [tblRR25_(Third-party)]
GROUP BY [tblRR25_(Third-party)].[Opco Code], IIf(ReturnVariableX()="ALL",[Opco Code] Like "*",ReturnVariableX());

Look at what the SQL would look like with the ReturnVariableX Evaluated

For All

SELECT [tblRR25_(Third-party)].[Opco Code], [Opco Code] Like "*" AS Expr1
FROM [tblRR25_(Third-party)]
GROUP BY [tblRR25_(Third-party)].[Opco Code], [Opco Code] Like "*";

For the In List

SELECT [tblRR25_(Third-party)].[Opco Code], In ("Item1","Item2") AS Expr1
FROM [tblRR25_(Third-party)]
GROUP BY [tblRR25_(Third-party)].[Opco Code], In ("Item1","Item2");

Your Function is returning "Where" Criteria not columns