Insert field into output file name (1 Viewer)

bren1519

New member
Local time
Today, 08:34
Joined
Sep 10, 2021
Messages
4
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:34
Joined
Oct 29, 2018
Messages
19,172
Hi. Does the resulting report only have one value for LastPay?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:34
Joined
Feb 19, 2002
Messages
36,889
Is the date calculated in the report? If so, you can try putting code in the appropriate format section of the report to place the code in an unbound control on the form:

Forms!someformname!txtSomeDate

Set the format of txtSomeDate to shortdate so that Access knows it is a date. Then format the date as you concatenate it.
 

Users who are viewing this thread

Top Bottom