Filter is Too Long

[mtompkins]

Gents -

I have the specific problem as outlined here:

http://www.tek-tips.com/viewthread.cfm?qid=1102402

I am able to filter a report based on a SQL building loop, however when too many records are indicated it fails with the Filter Is Too Long error.

On the referenced archived thread the advice is ...

gol4 (TechnicalUser) 3 Aug 05 17:29
drop the or and change the string you are building to a comma seperated values and use the in clause to select

Table_Facilities.Info_FacilityCode in (1,3,4,7)

for the issue shown.

My code that needs to be reworked is

Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

Dim strDocName As String
Dim frm As Form
Dim ctl As Control
Dim varItm As Variant
Dim intI As Integer
Dim ListRow, Col, FldName, Quote, StrOR, strWhere As String


Set frm = Forms("Form1")
Set ctl = frm.List0

FldName = " (Field3)= "

Quote = """"

StrOR = " or "

   For Each varItm In ctl.ItemsSelected
        For intI = 0 To ctl.ColumnCount - 1
            ListRow = ctl.Column(intI, varItm)
            If Not IsNumeric(ListRow) Then
                Col = Col & FldName & Quote & ListRow & Quote & StrOR
            End If
        Next intI
    Next varItm

strWhere = Left(Col, Len(Col) - 3)

    strDocName = "rpt" & Me.Combo39
    DoCmd.OpenReport strDocName, acPreview, , strWhere

    

Exit_Command33_Click:
    Exit Sub

Err_Command33_Click:
    MsgBox Err.Description
    Resume Exit_Command33_Click
    
End Sub

If someone is familiar on how to replace my code with the "IN" clause I would be grateful for the help.

Many thanks.

 

[PHV]

Set ctl = frm.List0
For Each varItm In ctl.ItemsSelected
    For intI = 0 To ctl.ColumnCount - 1
        ListRow = ctl.Column(intI, varItm)
        If Not IsNumeric(ListRow) Then
            Col = Col & ",'" & ListRow & "'"
        End If
    Next intI
Next varItm
strWhere = "Field3 In (" & Mid(Col, 2) & ")"
DoCmd.OpenReport "rpt" & Me!Combo39, acViewPreview, , strWhere

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mtompkins]

PHV you are the best, thanks.

Works perfectly.