Re: [MSAccessProfessionals] Access report, need to pass parameters to SQL Server back end

The previous messages which didn't get included mentioned SQL Server which it is much more efficient to use a saved pass-through. Also this might have been for a subreport which can't use a filter like a main report. 

Duane

Sent from my mobile

On Jul 17, 2020, at 5:35 PM, Arnelito Puzon <agpuzon@gmail.com> wrote:



You do not NEED to change the Query everytime, especially if other reports is based on it.

What you need is to Filter the Report when you Open it:

Private Sub OK_Click()

 

    Dim strSQL As String

    'no need to change the Query SQL

    'Dim strOldSQL As String

 

    If Not IsNull(Me.txtStart) Then

        strSQL = strSQL & "MaxOfReleaseDate >= #" & Format(Me.txtStart, "mm\/dd\/yyyy") & "# AND "

    End If

    

    If Not IsNull(Me.txtEnd) Then

        strSQL = strSQL & "MaxOfReleaseDate <= #" & Format(Me.txtEnd, "mm\/dd\/yyyy") & "# AND "

    End If

      

    If Not IsNull(Me.cbQRFType) Then

        strSQL = strSQL & "QRFTypeDescr = '" & Me.cbQRFType & "' AND "

    End If

  

    If Not IsNull(Me.txtClassCode) Then

        strSQL = strSQL & "[Class] = '" & Me.txtClassCode & "' AND "

    End If

    

    If Len(strSQL) > 0 Then

        strSQL = Trim(Left(strSQL, Len(strSQL) - 5))

    End If

    

    ' On the Report, you Add the Sort Order on it's Property

    'strSQL = strSQL & "ORDER BY [SN/LotStart], WorkOrderNo;"

  

    ' you DO NOT NEED to change the query,

    ' use strSQL to FILTER the report

    'strOldSQL = fChangeSQL("Instruments Query", strSQL)

 

    DoCmd.OpenReport ReportName:="Instruments Detail Report", view:=acViewPreview, WhereCondition:=strSQL

 

 

End Sub

--

Arnelito G. Puzon