Hi again,
You were very helpful with my last question and I have one more on this code (see below). I've added the current date to the end of the file name and would like to change that to a date field in my report. Can't get the syntax correct to do that and wondered if someone could point me in the right direction.
Field I would like inserted is Payroll.LASTPAY but not sure how to insert that into the code. Thanks! Brenda
Private Sub Command0_Click()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT Payroll.[DEPARTMENT] FROM Payroll;", dbOpenSnapshot)
'LOAD CURRENT DATE TO VARIABLE - IN FORMAT dd-mm-yyyy
Date1 = Format(Date, "dd-mm-yyyy")
Do While Not rst.EOF
strRptFilter = "[DEPARTMENT]= '" & rst![DEPARTMENT] & "'" 'assumes Department is a string
DoCmd.OpenReport "Payroll SME Reporting", acViewPreview, strRptFilter, acHidden
DoCmd.OutputTo acOutputReport, "Payroll SME Reporting", acFormatPDF, "C:\Users\bbrown\Desktop\PAYROLL\Payroll Reporting\" & "\" & rst![DEPARTMENT] & " " & Date1 & ".pdf"
DoEvents
DoCmd.Close acReport, "Payroll SME Reporting", acSaveNo
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub
You were very helpful with my last question and I have one more on this code (see below). I've added the current date to the end of the file name and would like to change that to a date field in my report. Can't get the syntax correct to do that and wondered if someone could point me in the right direction.
Field I would like inserted is Payroll.LASTPAY but not sure how to insert that into the code. Thanks! Brenda
Private Sub Command0_Click()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT Payroll.[DEPARTMENT] FROM Payroll;", dbOpenSnapshot)
'LOAD CURRENT DATE TO VARIABLE - IN FORMAT dd-mm-yyyy
Date1 = Format(Date, "dd-mm-yyyy")
Do While Not rst.EOF
strRptFilter = "[DEPARTMENT]= '" & rst![DEPARTMENT] & "'" 'assumes Department is a string
DoCmd.OpenReport "Payroll SME Reporting", acViewPreview, strRptFilter, acHidden
DoCmd.OutputTo acOutputReport, "Payroll SME Reporting", acFormatPDF, "C:\Users\bbrown\Desktop\PAYROLL\Payroll Reporting\" & "\" & rst![DEPARTMENT] & " " & Date1 & ".pdf"
DoEvents
DoCmd.Close acReport, "Payroll SME Reporting", acSaveNo
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub
