OpenReport WhereClause is too long

[rhewitt]

I'm trying to feed a long string into the Where Condition of the DoCmd.OpenReport command. I'm getting a runtime error that my Where Condition is too long to run.



According to the docs:

The maximum length of the WhereCondition argument is 32,768 characters (unlike the Where Condition action argument in the Macro window, whose maximum length is 256 characters).

In the code below I build a Where Condition based on the results of a query. The Where Condition is a string like below (this is a shorter version than in production):

ProjectID = 6 Or ProjectID = 6 Or ProjectID = 6 Or ProjectID = 19 Or ProjectID = 178 Or ProjectID = 178 Or ProjectID = 179 Or ProjectID = 179 Or ProjectID = 179 Or ProjectID = 180 Or ProjectID = 180

You'll notice that there are some duplicates, which could be removed to shorten the string with some more logic. The problem is my FULL string is only ~5,000 characters, which is substantially less than the upper limit.

Code to generate the query string:

 
Private Sub cboxRegion_AfterUpdate()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim selectedRegion As String
    Dim upperLimit As Integer
    Dim lowerLimit As Integer
    Dim rsQuery As String
    Dim query As String
    Dim count As Integer
    
    Me.tboxQuery = Null
    
    If Not IsNull(Me.cboxRegion) Then
        selectedRegion = Me.cboxRegion
    End If
    
    Set db = CurrentDb()
    
    ' Find all the projects that the selected individual has been assigned to
    rsQuery = "SELECT dbo_tblProjects.ProjectID " & _
            "FROM (dbo_tblProjects INNER JOIN dbo_junctionProjectIndividuals ON dbo_tblProjects.ProjectID = dbo_junctionProjectIndividuals.ProjectID) " & _
            "INNER JOIN dbo_luIndividuals ON dbo_junctionProjectIndividuals.IndividualID = dbo_luIndividuals.IndividualsID " & _
            "WHERE dbo_luIndividuals.Region = '" & selectedRegion & "'; "
    
    Set rs = db.OpenRecordset(rsQuery, dbOpenDynaset, dbSeeChanges)
    rs.MoveLast
    upperLimit = rs.RecordCount
    
    If (rs.RecordCount = 0) Then
        MsgBox "This individual is not yet assigned to any projects!"
    Else
        lowerLimit = 0
        count = 0
    End If


    rs.MoveFirst
    'Build Query String for report
    Do While Not rs.EOF

        ' Build a query string
        If count < upperLimit - 1 Then
            query = query + "ProjectID = " & rs!ProjectID & " Or "
        Else
            query = query + "ProjectID = " & rs!ProjectID & " "
        End If
        
        count = count + 1
        
        'Move to the next record. Don't ever forget to do this.
        rs.MoveNext
    Loop
    
    Me.tboxQuery = query
    rs.Close
    Set rs = Nothing
End Sub

Code to generate the report:

Private Sub Command10_Click()
    If IsNull(Me.tboxQuery) Then
        MsgBox "Please select a Region before attempting to run a report.", vbQuestion, "No Region selected"
    Else
        DoCmd.OpenReport "rpt_Complete", acViewReport, , Me.tboxQuery
    End If
End Sub

[rhewitt]

Is it possible to use a WHERE IN() clause for the Where Condition in the OpenReport command? The following throws a syntax error, but is substantially shorter:

Syntax Error (missing operator) in query expression

WHERE dbo_tblProjects.ProjectID IN(6, 6, 6, 19, 178, 178, 179, 179, 179, 180, 180, 180, 181, 181, 181, 182, 182, 182, 183, 183, 184, 184, 184, 185, 185, 185, 186, 186, 186, 187, 187, 188, 188, 188, 189, 189, 190, 190, 192, 192, 192)

[rhewitt]

Solution: The Where IN clause does work here. You don't need the "WHERE" statement as I suppose it's assumed when used as part of the Where Condition of the OpenReport Command.

Passing the following into the Where Condition worked successfully:

ProjectID IN(6, 6, 6, 19, 178, 178, 179, 179, 179, 180, 180, 180, 181, 181, 181, 182, 182, 182, 183, 183, 184, 184, 184, 185, 185, 185, 186, 186, 186, 187, 187, 188, 188, 188, 189, 189, 190, 190, 192, 192, 192)