Using a String in a query as the Criteria 1

[joebb3]

I am trying to set the criteria in a query to only show the items selected in a list box. Here is my problem.

When the query tries to read, the value of the function returning the criteria is:

"'0101AA' or '0101AB' or '0101AC' or '0102AA' or '0102AB'"
ie. the criteria I want, however it is surrounded by quotes.

basically, I just want my function to return:
'0101AA' or '0101AB' or '0101AC' or '0102AA' or '0102AB'

Help! (I know I've done this before, but I can't remember which project it was)

Joe

Here are the coding particulars if you are interested...

In the query, I have:

ReportCLINS()

as the criteria of one of the columns.

I get ReportCLINS() from a function that reads my List Box ("List0"):

Function ReportCLINS()
    Dim ctlSource As Control
    Dim strItemsTO As String
    Dim intCurrentRow As Integer
    Dim FilterItems As Variant
    FilterItems = 0
    Set ctlSource = Forms!TotalOrder!List0
    'Set ctlDest = frm!lstDestination
    
    For intCurrentRow = 0 To ctlSource.ListCount - 1
        If ctlSource.Selected(intCurrentRow) Then
            If FilterItems = 0 Then
            strItemsTO = "='" & ctlSource.Column(0, _
                 intCurrentRow) & "' or '"
            Else
            strItemsTO = strItemsTO & ctlSource.Column(0, _
                 intCurrentRow) & "' or '"
            End If
            FilterItems = FilterItems + 1
        End If
    Next intCurrentRow
    strItemsTO = Trim(Left(strItemsTO, Len(strItemsTO) - 5))
    Set ctlSource = Nothing
    Set ctlDest = Nothing

ReportCLINS = strItemsTO
End Function

 

[MajP]

I would be suprised if your code is actually putting in a set of quotes.
Please add a debug.print at the end of the function to verify.

Also are you using this as a criteria for a query. It will not work. I believe your problem is you are trying to do something like

where myField = '0101AA' or '0101AB' or '0101AC' or '0102AA' or '0102AB'

need something more like

where myField = '0101AA' or myField = '0101AB' or myField = '0101AC' or myField = '0102AA'

 

[dhookom]

You can't use a function to return a list of values like this. There are several examples in the queries FAQ

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Anyway, use the In operator.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[joebb3]

Wow!!! My thread quickly became a "Who's who" of Tek-tips!!! Thanks for the replies!

MajP: You're right... The debug print doesn't show the "". also, I'm doing this in Query Design, not as a sql statement. I use functions this way all the time, just not from a listbox where I have to build the criteria.

dhookom: I knew I had seen those FAQs before! I was just looking in the wrong section... Looks like I'm gonna go with the temp recordset solution. Thanks!

PHV: I love your "think for yourself" style! Always have! Along with dhookom's suggestion, I will look at the operator as well.

Always appreciative!
Joe

I will post my solution (after I have it) for posterity.

 

[joebb3]

Solution:

Using this FAQ (

http://www.tek-tips.com/faqs.cfm?fid=1927)

I run the following function to load the table:

Function ReportCLINS()
    Dim ctlSource As Control
    Dim strItemsTO As String
    Dim intCurrentRow As Integer
    Dim FilterItems As Variant
    Dim conDatabase As ADODB.Connection
    Dim SQL As String
    Dim db As Database
    Dim db1r As Recordset
    
    FilterItems = 0
    Set db = CurrentDb
    Set db1r = db.OpenRecordset("TotalOrderReportTemp")
    Set ctlSource = Forms!TotalOrder!List0
    Set conDatabase = Application.CurrentProject.Connection
   
    SQL = "DELETE * FROM [TotalOrderReportTemp]"
    conDatabase.Execute SQL
    DoEvents
    conDatabase.Close
    Set conDatabase = Nothing

    For intCurrentRow = 0 To ctlSource.ListCount - 1
        If ctlSource.Selected(intCurrentRow) Then
            With db1r
            .AddNew
            db1r!CLIN = ctlSource.Column(0, intCurrentRow)
            .Update
            End With
            FilterItems = FilterItems + 1
        End If
    Next intCurrentRow
    Set ctlSource = Nothing
    Set ctlDest = Nothing

End Function

The table has only one field (CLIN) which now holds all of the selected items in the Listbox.

After that I just build my query to display only the records that match the items that are contained in the Temp Table.

I'll clean up the table later in my code (I'll be moving the Delete SQL statement to clean out the table AFTER the code executes... Its where it is now for debuging purposes). I use "FilterItems" to ensure that at least one item was selected. If "FilterItems = 0" then I present an error box later in the code.

Thanx to everyone for putting me on the right track!
Joe