Form field used as criteria for query

[ziccardi]

When my query is executed it produces no results. However, if I copy and paste the criteria from the information populated and referenced in my query, it works fine.

The criteria needs to be numbers. eg. In (1,2) The text in my criteria field is:

In ([Forms]![frm_RateLoad]![Criteria])

Here is the code:

Private Sub VIEW_NewRate_Click()
On Error GoTo Err_VIEW_NewRate_Click
    Dim MyDB As DAO.Database
    Dim i As Integer
    Dim vs As String
    Dim strSQL As String
    Dim strWhere As String
    Dim strIN As String
    Dim flgSelectAll As Boolean
    Dim varItem As Variant
    Dim rstTempTbl As New ADODB.Recordset
 
    If FNU_Choice.ItemsSelected.Count = 0 Then
        MsgBox "You must make a selection(s) from the list" _
               , , "Selection Required !"
    Else
        vs = ConnectSelected(FNU_Choice, ",", 0)
        'Field referenced in query
        Me.Criteria = vs
        DoCmd.OpenQuery "Benefit_Rate_Summary - View NEW Rate", acViewNormal
 
    End If
    'Clear listbox selection after running query
    For Each varItem In Me.FNU_Choice.ItemsSelected
        Me.FNU_Choice.Selected(varItem) = False
    Next varItem
 
 
Exit_VIEW_NewRate_Click:
    Exit Sub
 
Err_VIEW_NewRate_Click:
        'Write out the error and exit the sub
        MsgBox Err.Description
        MsgBox Err.Number
        Resume Exit_VIEW_NewRate_Click
 
End Sub
 
Public Function ConnectSelected(ListName As ListBox, _
                        Optional ByVal sDelimiter As String = ",", _
                        Optional ByVal iColumn As Integer = 0) As String
 
   Dim sConnect   As String
   Dim vID        As Variant
   Dim v          As Variant
 
   If ListName.ColumnCount <= (iColumn - 1) Then
      Err.Raise vbObjectError + 1501, , "The specified column doesn't exist."
   End If
 
   For Each v In ListName.ItemsSelected
      vID = ListName.Column(iColumn, v)
      sConnect = sConnect & CInt(vID) & sDelimiter
   Next v
 
   If Len(sConnect) Then
      ConnectSelected = Left(sConnect, (Len(sConnect) - Len(sDelimiter)))
   Else
      ConnectSelected = ""
   End If
 
End Function

THANK you for the help

[Stewart Ross]

Hi ziccardi. Are you executing the query in code? If you are, the In statement will not work as you have it at present. If you have defined the SQL for the query as a string you would need to split the string to include the value of your form control, not a reference to the control itself, like this:

strSQL = strSQL & "WHERE [yourfield] In (" & [Forms]![frm_RateLoad]![Criteria] & ")"

I'm not sure what relevance the VBA code you posted has to your problem. If the query is not returning any results when you run it but it does from the query editor then the query and how it is constructed is the bit we need to see!

-Stewart

[ziccardi]

I need the query to be easily updatable, so I do not think that using sql will work. The query itself is pretty complex with many joins. Anyway, I changed my approach and populate the information to another table. I connected this table in the query to the field instead of using criteria. It seems to be working great. Thank you for your response.