NZ date query doesn't work

Question

I have a code that generates and save a report as a pdf. I need to name the pdf as [lastname], [firstname] - [payroll_date] . My code works if it's only last name and first name. If I added the payroll_date , it will not generate and save the pdf.

Heres my code:

Set rs = CurrentDb.OpenRecordset("SELECT [ID],[last_name],[first_name],[payroll_date] FROM [Payslip_upload]", dbOpenSnapshot)
With rs
    If .RecordCount <> 0 Then    'Make sure we have record to generate PDF with
        'Open the Report
        DoCmd.OpenReport sReportName, acViewPreview, , , acHidden
        'Define a report object so we can manipulate it below
        Set rpt = Reports(sReportName).Report
        .MoveFirst
        Do While Not .EOF
            'Build the PDF filename we are going to use to save the PDF with

            sFile = Nz(![last_name], "") & ", " & Nz(![first_name], "") & Nz(![payroll_date], "") & ".pdf"
            sFile = sFolder & sFile
            'filter the report to the specific record or criteria we want
            rpt.Filter = "[ID]=" & ![ID]
            rpt.FilterOn = True
            DoEvents 'This is critical!!!!
            'Print it out as a PDF
            DoCmd.OutputTo acOutputReport, sReportName, acFormatPDF, sFile, , , , acExportQualityPrint

Answer 1

Probably payroll_date contains some characters, like ‘/’, which are not allowed. Try replacing the characters, for example:

. . .
Dim d as String
d = Replace(Replace(Replace(Nz(![payroll_date], ""), "/", "-"), ".", "-"), ":", "-")
sFile = Nz(![last_name], "") & ", " & Nz(![first_name], "") & d & ".pdf"
. . .

If this does not help, then show some sample values of payroll_date.