using VBA to open report with the filter set

[icemonster]

hello,

here is my code for a form''s list box,

Function setVisitDueList()
'set starting sql statement
        strStartSql2 = "SELECT qryVisitListVBA2.ID, qryVisitListVBA2.SupervisoryVisitID, qryVisitListVBA2.[Homemaker Name], " _
                       & "qryVisitListVBA2.HireDate, qryVisitListVBA2.InitialVisit, qryVisitListVBA2.SupervisoryVisitDate, " _
                       & "qryVisitListVBA2.ClientID, qryVisitListVBA2.TypeofHomemaker, qryVisitListVBA2.NextVisitOn, " _
                       & "qryVisitListVBA2.VisitDate, qryVisitListVBA2.supervisor FROM qryVisitListVBA2 "
          

If Not IsNull(Me.txtSearch2) Then
    strWhereSql2 = "WHERE qryVisitListVBA2.[Homemaker Name] Like '*" & Me.txtSearch2 & "*'"
Else
    strWhereSql2 = ""
End If
    

If Not IsNull(Me.txtStartDate2) And Not IsNull(Me.txtEndDate2) Then
    'read the dates selected in the variables
    dtStartDate2 = Me.txtStartDate2
    dtEndDate2 = Me.txtEndDate2
    If strWhereSql2 = "" Then
        strWhereSql2 = " WHERE VisitDate Between #" & dtStartDate2 & "# " _
                    & "And #" & dtEndDate2 & "# "
    Else
        strWhereSql2 = strWhereSql2 & "AND VisitDate Between #" & dtStartDate2 & "# " _
                    & "And #" & dtEndDate2 & "# "
    End If
End If

strSortOrderSql2 = " ORDER BY qryVisitListVBA2.VisitDate;"

strSQL2 = strStartSql2 & strWhereSql2 & strSortOrderSql2
With Me.lstSupervisoryVisit
    .RowSource = strSQL2
    .Value = Null
End With

End Function

now, someone told me back then, i can also use the criteria here to open a report, how do i do that? do i use a set link? please help. thanks.

 

[pbaldy]

A wherecondition for OpenReport is an SQL "WHERE" clause without the word "where". Your strWhereSql2 string would therefore work as a wherecondition, as long as you took the word "WHERE" out.

 

[icemonster]

sorry. am not getting it, so if the command where to open was like

DoCmd.OpenReport "rptReport", acPreview, Where = strSQL2?

 

[pbaldy]

Well, that's not the variable I mentioned, and you would simply have it in the position for the wherecondition (which that isn't). VBA Help on OpenReport will help with the proper positioning.

 

[The_Doc_Man]

Here is a snippet I used in a recent project.

Public Sub OpenFilteredReport(sCrit As String, sFilt As String, sRptn As String)

Dim lRecCnt As Long                 'evaluate record count

    lRecCnt = DCount("[AcSrPK]", "roqActionDetails", sCrit)
    
    If lRecCnt > 0 Then             'returns any data at all?
        DoCmd.OpenReport sRptn, acViewPreview, , sCrit, acWindowNormal, sFilt
    Else
        MsgBox "The currently selected filtration combination, " & vbCrLf & _
            "'" & Trim$(sFilt) & "', " & vbCrLf & _
            "returns no data." & vbCrLf & vbCrLf & _
            "We will not open the report.", vbOKOnly + vbInformation, _
            "No data in requested report"
    End If

End Sub

I pass in a report name (sRept), the criteria clause (sCrit) which is a WHERE clause without the word WHERE, and a text string (sRptn) that becomes a part of my report title. sRptn is an English statement of the nature of the filter criteria. Notice the DCount beforehand, which uses sCrit to check for "no data" and doesn't even bother to open the report. The domain name is also the name of the query on which my filtered reports are based. The criteria clause that works in a DCount will also work in a report filter, as they have the same exact syntax. If knowing that similarity helps, then perhaps you can do what you need a little more confidently.

 

[icemonster]

ok so i did this

Private Sub cmdPrint2_Click()
DoCmd.OpenReport "rptVisitDueList", acViewPreview, , strSQL2
End Sub[/CODE/

but i am getting a sytanx error, basically, the query for that listbox is the same source for the report.

 

[pbaldy]

Twice I've mentioned using the variable that only contains the WHERE clause, you keep using the variable containing the full SQL string. Once you change that, is the variable public so it will still be in scope (have a value)? Have you gotten rid of the word "WHERE"?

 

[icemonster]

what do you mean get rid of it? like totally remove the WHERE in strWhereSql2, so it will be strSql2? sorry. am not really adept at this.

 

[pbaldy]

An SQL where clause looks like:

WHERE FieldName = 123

Two of us have mentioned that a wherecondition is an SQL where clause without the word where, which would look like:

FieldName = 123

You need to use the string that only includes the criteria, not the string that includes the entire SQL, and you need to either not include the word "where" when you build that string or eliminate it when you use the string for this.

 

[icemonster]

so basically, if i want to filter the date on the report it would be something like

DoCmd.OpenReport "rptReport", acPreview, , Date = strWhereSql3

is this right now?

 

[pbaldy]

Does it work?

What are the contents of the variable at the point that runs (you can use Debug.Print if you don't know how to determine that).

 

[icemonster]

Private Sub cmdPrint2_Click()
DoCmd.OpenReport "rptVisitDueList", acViewPreview, , VisitDate = strWhereSql2
End Sub

i wrote it like that and still nothing

i want to be able to filter the dates or even the search text to the print.