filter too long

[mikemaki]

I have a report that opens by a DoCmd.OpenReport statement. To build the WhereFrom filter I iterate through a list box with the following statement:

strWhere = "TableID = "
With Me!VPProjects
For intI = 0 To .ListCount - 1
If .Selected(intI) Then
strWhere = strWhere & .Column(0, intI) & " Or TableID = "
End If
Next
End With

intLength = Len(strWhere)
strWhere = Left(strWhere, intLength - 14)
DoCmd.Close acForm, "frmSelectVPs"

stDocName = "rptVPFinanceSpreadsheet"
DoCmd.OpenReport stDocName, acPreview, , strWhere
Forms!frmMainScreen!ReportName = stDocName


The ListBox contains projects under a VP. For most VP's the filter is less than 2,000 characters and works fine. But for a couple the string is more than 4,000 characters and I receive an error that the filter string is too long. Supposedly the max for a filter string is over 32,000 bytes. does anyone know why my string is in error?

 

[pbaldy]

I'm not sure about the limits on filter (actually wherecondition) length, but you can significantly reduce the length of yours by building an IN() clause. Use the same methodology but build a string that looks like this when it's done:

TableID IN(123,456,789)

instead of

TableID = 123 OR TableID = 456 OR TableID = 789

 

[mikemaki]

Thanks Paul! It works like a charm. It's also a lot simpler to use. I know I will be using in the future.

Mike