Open a report and apply a where clause

[cmmrfrds]

I have a button in a popup form that creates a where clause to be used when opening a report. It does not apply the where clause. How do I get this to happen.

Code under button.
Dim whereclause As String, args As String
Dim DocName As String
''whereclause = "rqStatus = 1"
DocName = "rptManagerClientHoursSummary"
DoCmd.OpenReport DocName, acViewPreview, , whereclause

Code in Report.
Private Sub Report_Open(Cancel As Integer)
'- set filter
Me.FilterOn = True

Debug.Print "query = "; Me.RecordSource
Debug.Print "filter = "; Me.Filter

End Sub

 

[Remou]

In your example, thw whereclause is commented, do you mean:

whereclause = "rqStatus = 1"

http://lessthandot.com

 

[cmmrfrds]

Yes. When I was trying the code it was not commented out. Thank you.

 

[Remou]

The where clause is not a filter. Do you find that the report includes records where rqStatus is not equal to 1?

http://lessthandot.com

 

[dhookom]

cmmrfrds,
Please clarify. Is your issue resolved or do you still need assistance?

You shouldn't need any code in your report that pertains to the filter. Using the WHERE CONDITION in the DoCmd.OpenReport will automatically set the Filter property and set the FilterOn to True.

You have a couple Debug.Print statements in your code but haven't told us the results.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Remou,
I believe the WHERE CONDITION does apply to the report's filter property. You can check this by simply adding a text box to the report and set its Control Source to:

  =[Filter]

This should display the WHERE CONDITION from the DoCmd.OpenReport method.

Duane
Hook'D on Access
MS Access MVP

 

[cmmrfrds]

Thank for your input.

I solved the problem by putting all the code including building the where clause in the onopen event of the report. I put the data I needed in 4 public variables and returned it through public functions.

Private Sub Report_Open(Cancel As Integer)
Dim whereclause As String, args As String
Dim sSql As String, fSQL As String
Dim DocName As String

whereclause = " Where "
If Len(Trim(pubEndDate)) > 0 Then
whereclause = whereclause & "TransfusionDate >= " & "#" & ReturnBegDate() & "#" _
& " And TransfusionDate <= " & "#" & ReturnEndDate() & "#"
End If
Debug.Print "where clause = "; whereclause

If Len(Trim(pubProviderID)) > 0 Then
If Len(Trim(pubEndDate)) > 0 Then
whereclause = whereclause & " And OrderingProvider = " & "'" & ReturnProviderID() & "'"
Else
whereclause = whereclause & " OrderingProvider = " & "'" & ReturnProviderID() & "'"
End If
End If
If Len(Trim(pubFacility)) > 0 Then
If Len(Trim(pubEndDate)) > 0 Then
whereclause = whereclause & " And Facility = " & "'" & ReturnFacility() & "'"
Else
whereclause = whereclause & " Facility = " & "'" & ReturnFacility() & "'"
End If
End If

sSql = "Select * from qryBloodInfoReport "
''''Debug.Print "final where = "; whereclause
fSQL = sSql & whereclause
'''''Debug.Print "query = "; fSQL
Me.RecordSource = fSQL

End Sub

 

[dhookom]

I think this is sdrawkcab (backwards). Your code doesn't even include your initial post regarding "rqStatus = 1".

Also, your results won't be as expected if pubProviderID has a value and pubEndDate doesn't and pubFacility does (I think).

I build my where clause like:

    Dim whereclause as String
    whereclause = "Where 1=1 "
    If Len(Trim(pubEndDate)) > 0 Then
        whereclause = whereclause & " AND TransfusionDate >= #" & ReturnBegDate() & _
         "# And TransfusionDate <= #" & ReturnEndDate() & "# "
    End If
    If Len(Trim(pubProviderID)) > 0 Then
        whereclause = whereclause & " And OrderingProvider = '" & ReturnProviderID() & "' "
    End If
    If Len(Trim(pubFacility)) > 0 Then
        whereclause = whereclause & " And Facility = '" & ReturnFacility() & "' "
    End If

You can always begin each new where condition with " AND " since it is always appropriate.


Duane
Hook'D on Access
MS Access MVP