Reports based on queries w/ WHERE clauses

  • Thread starter Thread starter Pat Dools
  • Start date Start date
P

Pat Dools

I am struggling with launching reports from behind a Command button on a form
where a query with a WHERE clause is involved. Launching the report from the
Database Window is no problem, but somehow putting the right code behind a
button is preventing the report from launching with data showing properly.

Here is a sample of some code I'm using that is not working:

Private Sub CommandOpenVisitPerSession_Click()
DoCmd.OpenReport "rVisit_Per_Session", acViewPreview, "q_Visit_Per_Session",
"Visit_Date between [Begin Date] and [End Date] and Session_ID =1"
End Sub

I am just about ready to release this version (not the final one yet) but I
can't do so until they can use the 'Reporting Switchboard' properly. Thank
you in advance, I've been searching for examples of how to do this, but none
quite match what I'm doing. In the above example, I need the user to get
prompted for a date range that [Visit_Date] falls in and where [Session_ID] =
1.

Thank you!
 
I am not aware of anyone specifying the query name in the DoCmd.OpenReport
I would add two text boxes on the form txtStart and txtEnd and change the
code to:


Private Sub CommandOpenVisitPerSession_Click()
Dim strWhere as String
strWhere = "Session_ID =1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND Visit_Date >=#" & _
Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND Visit_Date <=#" & _
Me.txtEnd & "# "
End If
DoCmd.OpenReport "rVisit_Per_Session", acViewPreview, , strWhere
End Sub
 
Back
Top