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