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

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

_._,_._,_

---

Groups.io Links:

You receive all messages sent to this group.

View/Reply Online (#115706) | Reply To Group | Reply To Sender | Mute This Topic | New Topic

Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_