Run access report including another non-associated attached report with same date range

Question

Three Access reports use two pass-through queries and a standard SELECT query and all work singly. The one main report is kicked off with a command button that takes two dates from the form for a date range. I can see those date parameters change in the ptq after each use. One of the attached reports runs based on another ptq using the supplied "default" dates for the date range (there's nothing that resets those dates of the ptq as the attached reports are called from the main report - that is my problem or disconnect I believe), and the other on a standard select query with a WHERE clause calling the dates from the form. Those two reports supply additional but different data to the main report. I don't believe they will be subreports with linked master and child fields as the main report data and attached report data are not associated. Problem is the date range is not being passed to the one ptq for the attached report so the date never changes and only ever comes back with the "default" date range that is in its ptq. Here are the two ptqs:

exec dbo.uspJobsCompletedThruPress '2019-10-07 05:30:00','2019-10-12 14:29:59'
exec dbo.uspJobsCompletedThruPress_SubReport '2019-10-14 05:30:00','2019-10-19 14:29:59'

The subreport sp returns a single record as expected.

I have tried using a SELECT query like the other attached report but that won't work because the sp's scalar returns no dates, it only takes the date parameters to build the scalar record.

How can I get the date parameters to pass to the attached report's ptq from the main called report?

The other issue is, these attached reports are located in the Report Footer section so their data will print immediately after the main report's list of records. However, since I am using two columns, the attached report's data always prints on the next page not at the bottom of the first column (usually) or the second column (given enough records), even when there is sufficient room at the end of the listed records. How can I make that attached report directly follow the first list? Should this be another question?

Albert D. Kallal Albert D. Kallal · Accepted Answer · 2019-10-31T20:05:57

Well, you can always setup the PT queryes BEFORE you launch the report.

So you could use some code like this:

Sub Mytestzz()


  Dim strDtStart     As String
  Dim strDtEnd       As String

  strDtStart = qudateT("some date source for start date")
  strDtEnd = qudateT("some date source for end date")


  With CurrentDb.QueryDefs("qryJobsPressPass")
     .SQL = "exec dbo.uspJobsCompletedThruPress " & strDtStart & "," & strDtEnd
  End With

  With CurrentDb.QueryDefs("qryJobsPressPassSub")
     .SQL = "exec dbo.uspJobsCompletedThruPress_SubReport " & strDtStart & "," & strDtEnd
  End With

  ' now launch your report.



End Sub

So, once you run the above, then the two PT queries used are now all setup, and ready to go. If you launch a report based on the above two queries, then they will respect the critera you have. Do keep in mind that you can add additional "where" clause to the open report, but the client side will still pull whatever data you have based on the above PT queries (a view is actually better - since you can not bother with the PT query, and simple send all of your criteria to the open report command and access will only pull down the pipe the records based on the passed criteria. This means your previous existing VBA code to supply filters to the report will work as before. And you tend to get just as good performance (if not better) by using a view as opposed to a PT query (especially if you are supplying additional criteria to the openreport command).

Also, because one will often format dates for sql server and PT queries, then I have a routine to format the date for me. In above, I used qudateSQLt (that includes time). So that handy dandy function used looks like this:

Public Function qudateSQLt(myDate As Variant) As String

  ' returns a formatted string of date, surrounded with # signs
  ' format is mm/dd/yyyy as required for sql, regardless of date setting

  If IsNull(myDate) = True Then
     qudateSQLt = ""
  Else
     ' use ISO date format
     qudateSQLt = "'" & Format(myDate, "yyyy-mm-dd HH:NN:SS") & "'"
  End If

End Function

Run access report including another non-associated attached report with same date range

1 Answers