Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Unanswered: Passing "IN" Clause From Function to Query Criteria?

    I am having trouble getting a query to run using the results from a Function that is parsing values from a multi-select list box on a form. If I create a simple function that outputs a single string, it works fine. But in my more complex function that does the parsing, it will not work (regardless is one or more items in the list box are selected). The query runs, but bring back no results.

    Simple Function w/ 1 Output:
    Code:
    Public Function myBuyer()
        myBuyer = "01"
    End Function
    Query:
    http://i.imgur.com/LG2FX5Z.png

    Results:
    OK


    Parsing Values from Multi Select List Box:
    Code:
    Public Function BuyerSelect()
        Dim i As Integer
        Dim strIN As String
    
        'Build the IN string by looping through the listbox
        For i = 0 To Forms!frmOpenPO_Final!listBuyer.ListCount - 1
            If Forms!frmOpenPO_Final!listBuyer.Selected(i) Then
                strIN = strIN & """" & Forms!frmOpenPO_Final!listBuyer.Column(0, i) & ""","
            End If
        Next i
    
        BuyerSelect = Left(strIN, Len(strIN) - 1)
    End Function
    Results:
    NONE

    I'm thinking it has something to do with how the result of the function is passed into the query criteria. An MsgBox(BuyerSelect) shows me "01","03" which is what you would put in the IN( ) when coding maually, but I think it is seeing the results as one big string rather than literally sticking the results in the IN( ).

    I know another option is to just create and launch a query within the code itself, but this query has a bunch of other criteria that is being grabbed from text boxes on the same form (using the standard syntax Forms!myForm!myTxtBox)

    Any ideas on how to do this?
    Last edited by clawlan; 01-21-14 at 13:51.

  2. #2
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    OK, so after much digging, I learned that passing language like "OR", "IN", etc into the query builder's criteria box is not allowed. So i have reverted to using DAO to create the query on the fly.

    Now, no matter that I do, I keep getting this "Item not found in this collection" error. Even if I comment everything out except a simple "select * from myTable", I still get that error. Any ideas?

    Code:
    Sub bTest_Click()
        On Error GoTo Err_cmdOpenQuery_Click
        Dim MyDB As DAO.Database
        Dim qdef As DAO.QueryDef
        Dim i As Integer
        Dim strSQL As String
        Dim strWhere As String
        Dim strIN As String
        Dim flgSelectAll As Boolean
        Dim varItem As Variant
    
        Set MyDB = CurrentDb()
    
        strSQL = "SELECT * FROM OpenPO_Main"
    
        'Build the IN string by looping through the listbox
        For i = 0 To listBuyer.ListCount - 1
            If listBuyer.Selected(i) Then
    '            If listBuyer.Column(0, i) = "All" Then
    '                flgSelectAll = True
    '            End If
                strIN = strIN & "'" & listBuyer.Column(0, i) & "',"
            End If
        Next i
    
        'Create the WHERE string, and strip off the last comma of the IN string
        strWhere = " WHERE Buyer in " & _
                   "(" & Left(strIN, Len(strIN) - 1) & ")"
    
        'If "All" was selected in the listbox, don't add the WHERE condition
        If Not flgSelectAll Then
            strSQL = strSQL & strWhere
        End If
    
        MyDB.QueryDefs.Delete "qryCompanyCounties"
        Set qdef = MyDB.CreateQueryDef("qryCompanyCounties", strSQL)
    
        'Open the query, built using the IN clause to set the criteria
        DoCmd.OpenQuery "qryCompanyCounties", acViewNormal
    
    
    Exit_cmdOpenQuery_Click:
        Exit Sub
    
    Err_cmdOpenQuery_Click:
    
        If Err.Number = 5 Then
            MsgBox "You must make a selection(s) from the list" _
                   , , "Selection Required !"
            Resume Exit_cmdOpenQuery_Click
        Else
            'Write out the error and exit the sub
            MsgBox Err.Description
            Resume Exit_cmdOpenQuery_Click
        End If
    
    
    End Sub
    EDIT: OK, I figured out this error is coming from this line:

    Code:
    MyDB.QueryDefs.Delete "qryCompanyCounties"
    If I comment it out, it will run the first time, but in subsequent times, I get the error "Object Already Exists", so I have to un-comment that line. Is there a way to check to see if it exists and use an if statement to run or skip that DELETE code?
    Last edited by clawlan; 01-21-14 at 17:13.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the in clause use the form expression IN (value1, value2, .... value_n);

    Dim strIN As String
    strIn = "in ("
    'Build the IN string by looping through the listbox
    For i = 0 To Forms!frmOpenPO_Final!listBuyer.ListCount - 1
    If Forms!frmOpenPO_Final!listBuyer.Selected(i) Then
    strIN = strIN & "'" & Forms!frmOpenPO_Final!listBuyer.Column(0, i) & "', "
    End If
    Next i
    'chop off the trailing , + space
    strIn = left(strin,len(strin)-2) & ")"
    'lastly check there are some terms in strin
    if len(strin) <= 6 then ' we have no values
    'as this is a string valeu we are looking for
    strin = " like '%'
    endif
    'prefix strin with the column you want to look for and there y'go
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    thanks for the response. Thats what i ended up doing and works great. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •