Create report from filtered form

[SueBK]

We're using the following code to try and create a report from a mulit-variable search form. We believe its "supposed to" work - but (guess what!) it doesn't. Any advice would be appreciated.

THE SEARCH BUTTON

Private Sub btnSearch_Click()
Me.FrmSubPCGSearch.Form.RecordSource = BuildFilter()
Me.FrmSubPCGSearch.Requery
End Sub

THE FILTER

Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varItem As Variant
    Dim intIndex As Integer
 
    varWhere = Null  ' Main filter
    
    ' Check for ProjectID
    If Me.cmboProjectID > 0 Then
        varWhere = varWhere & "[ProjectID] = " & Me.cmboProjectID & " AND "
    End If
    
    ' Check for Issue Category
    If Me.cmbIssueCategoryID > 0 Then
    varWhere = varWhere & "[CategoryID] = " & Me.cmbIssueCategoryID & " AND "
    End If
    
    ' Check for Issue Priority
    If Me.cmboIssuePriorityID > 0 Then
    varWhere = varWhere & "[PriorityID] = " & Me.cmboIssuePriorityID & " AND "
    End If
    
    ' Check for Issue Resolution Status
    If Me.cmbResolutionStatusID > 0 Then
    varWhere = varWhere & "[StatusID] = " & Me.cmbResolutionStatusID & " AND "
    End If
    
 ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
     End If
  
    BuildFilter = "SELECT * FROM qryAllPCGData " & varWhere
 
End Function

THE REPORT BUTTON

Private Sub Command38_Click()
  
Dim stDocName As String
Dim stLinkCriteria As String
 
stDocName = "rptqryAllPCGData"
stLinkCriteria = "[ProjectID]=" & Me![ProjectID]
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
 
End Sub

 

[pbaldy]

I guess since you believe it's supposed to work, I'll ask what isn't working? It seems obvious that the form is being filtered on up to 4 fields, and the report only 1. If you want the report filtered the same as the form, you'd have to filter on the same 4 fields.

 

[Galaxiom]

The problem is in the BuildFilter function which appears to have been adapted to SQL from a function used to construct a clause for a filter or OpenArgs string.

As an SQL query it needs to be terminated with a semicolon.

 

[pbaldy]

The semi-colon is optional in Access SQL. I never include it when building SQL in VBA.