Printing parameters on report (1 Viewer)

[M Wise]

I want the parameters to print out on the report. For instance, if the parameters on a query linked to a report ask for [earliest date] and [latest date], then I want this to print on the report: "From [earliest date] to [latest date]." How do I get this variable info to print on the report?

 

[KDg]

Hi,

my solution was to do the following. It prints the dates in two text boxes at the top of the report

Private Sub GroupHeader0_Print(Cancel As Integer, PrintCount As Integer)
Let Me!txtStart = "From :" & MyStartDate
Let Me!txtEnd = "To :" & MyEndDate
End Sub

Private Sub Report_Open(Cancel As Integer)
Dim strSQL As String
On Error GoTo invDate
Let MyStartDate = InputBox("what date would you like to start from?", "START DATE")
Let MyEndDate = InputBox("what date would you like to end on?", "END DATE")

On Error GoTo Oops
Let strSQL = "SELECT [Employee List].[Team Leader], Data.Payroll, [Name]" & Chr(38) & Chr(34) & Chr(32) & Chr(34) & Chr(38) & " [Surname] AS xName, ReasonCodes.ReasonScript, Sum(Data.OutDur) AS SumOfOutDur " _
& "FROM [Employee List] INNER JOIN (ReasonCodes INNER JOIN Data ON ReasonCodes.ReasonCode = Data.ReasonCode) ON [Employee List].[Payroll Number] = Data.Payroll " _
& "WHERE (((Data.LogOutDate) >= #" & MyStartDate & "# And (Data.LogOutDate) <=#" & MyEndDate & "#)) " _
& "GROUP BY [Employee List].[Team Leader], Data.Payroll, [Name]" & Chr(38) & Chr(34) & Chr(32) & Chr(34) & Chr(38) & " [Surname], ReasonCodes.ReasonScript;"
'Debug.Print strSQL
Me.RecordSource = strSQL

Exit Sub
Oops:
Dim t As Boolean
Let t = StdError(Err.Number, Err.Description)
Exit Sub


This idea comes from someone else on this forum ( probably either Pat or Travis, but if not my apologies ). Since moving the query into VBA instead of qdf I haven't noticed any difference in speed and it gives you much more freedom to play about with it and present whatever you want. You'll need to ignore the stderror function as its not std VBA

HTH

Drew

 

[dennyryan]

Another approach is to use the paramter in setting a calculated field in your query:

EarlyDate: [Enter Earliest Date]

You can then refer to EarlyDate in your report as you would with any other column from your query.

You won't be prompted a second time for the parameter as long as it has the same exact format every time you use it in your query (Cut & Paste should take care of that detail). You can use the paramter multiple times in the same query, the user input will be applied to all instances.

Denny