Data type causing Complex Query Error (3701)

[nuke_girl]

I am trying to create a list box in a form from which you can either select multiple criteria or, if it is left blank, select the whole list. I got help from Mile here to build the code:

Option Compare Database
Option Explicit

Private Sub OK_Click()

    On Error GoTo Err_OK_Click
    
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim var As Variant
        
    Forms![frmMultiGroup].Visible = False
    
    Set db = CurrentDb
    If Me.Group.ItemsSelected.Count = 0 Then
        strSQL = "SELECT ...ORDER BY ...;"
    Else
        strSQL = "SELECT ... IN ("
        For Each var In Me.Group.ItemsSelected
            strSQL = strSQL & Me.Group.Column(0, var) & ","
        Next
        strSQL = Left(strSQL, Len(strSQL) - 1) & ")) ORDER BY DMS_COMMONCODES.CODE;"
    End If
    
    On Error Resume Next
    db.QueryDefs.Delete "qryGrpDose"
    On Error GoTo Err_OK_Click
    
    Set qdf = db.CreateQueryDef("qryGrpDose", strSQL)
           
Exit_OK_Click:
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub

Err_OK_Click:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_OK_Click
End Sub

The first part of the IF is working great; I get a *perfect* report for all the criteria from the list box.

My problem is that the For Each... Next statement is producing criteria that don't match the dataset within my query, so I'm getting Error 3701 "This expression is typed incorrectly, or it is too complex to be evaluated..." I thought that it was a problem of the leading zeros of my data being stripped away, but it's bigger than that, because even the values like "7300" aren't working. When I go back to my query after running the code, the criteria for the field I want the list box to filter has an entry like:

In (2, 3, 7300)

where it should have the values 002, 003, 7300. The only way to make it work is to go in manually and change the criteria to:

In ("002", "003","7300")

I have to leave var as Variant because I use it in the For Each... Next statement.
In the table (and thus the query), the data type for the field is text, because there are some entries that are not numerical.
I have no way to change anything in the tables. They aren't mine to change (I connect to an ODBC datasource).
Any help anyone can give me (including maybe a different way to get criteria from the list box?) would be much appreciated.

Thanks,
~ Nuke ~

 

[WayneRyan]

Nuke,

If they "look OK" in the listbox, try this:

For Each var In Me.Group.ItemsSelected
            strSQL = strSQL & "'" & Me.Group.Column(0, var) & "', "
        Next

Wayne

 

[nuke_girl]

Wayne,

I wish you could see the little dance I just did around my office.
Ok, so I didn't really dance, but I wanted to!

That worked PERFECTLY!!! I had actually thought of doing that. I don't know why I didn't try it.

THANK YOU SO MUCH!!!!!!!
This has been driving me nuts all week.

~ N ~