Exporting to PDF using fields as the name

(Follow-on from: MS access Split report and export each row as a PDF )

I am exporting files to PDF based on a a report. Initially to get it working I was naming the PDF files after the Table's ID field which worked but now I want to name it after the Job Number Field and the current date as number but when I change the button function to that it's not working. I think the syntax and formatting are fine but it still won't work.

The Code below is the entire button code which works and exports each row as its PDF

Private Sub Toggle2_Click()
   Dim sPath       As String
   Dim sFile       As String
   Dim sReport     As String
   Dim rs          As DAO.Recordset

   sPath = "C:\Users\murtaghd\Documents\files\"
   sReport = "export"
 
   Set rs = CurrentDb.OpenRecordset("SELECT * FROM Import")

   Do While rs.EOF = False

       DoCmd.OpenReport sReport, acViewPreview, , "ID = " & rs!ID, acHidden
       sFile = sPath & rs!ID & ".PDF"
    
       DoCmd.OutputTo acOutputReport, sReport, acFormatPDF, sFile
    
   
    
       DoCmd.Close acReport, sReport
    
       rs.MoveNext

    Loop

   rs.Close

   MsgBox "Export complete"
End Sub

I want to have it work where it uses job number and current date as the file name but it's not working with just the job number when

DoCmd.OpenReport sReport, acViewPreview, , "ID = " & rs!ID, acHidden
sFile = sPath & rs!Job_Number & ".PDF"

I tried formatting the Job_Number a few ways but every time there is an issue running the code

import

Table

The issue looks to be that you have a space in the column name. As noted, if spaces in column names can be avoided, then you should. Many reasons exist, but take this SQL query:

SELECT ID, City, First Name from tblCustomers

In above, we have a column with a space. But, SQL uses spaces for delimiters. So, in above, do we have a column called "First Name from tblcustomers"????

Or even called "First Name from" ????

So, spaces in column names tends to be a bad idea, since then we here, (and SQL) can't know when a column name stops, and then additional SQL key words (also separated by spaces) starts!

The general SQL solution is to introduce [ ] (square brackets) around the column name, so SQL can thus figure out when the column name ends, and additional things like ORDER BY etc. starts......

So, given you look to have a column name called "Job Number"?

Then your code needs to use this:

Do While rs.EOF = False

       DoCmd.OpenReport sReport, acViewPreview, , "ID = " & rs!ID, acHidden
       sFile = sPath & rs![Job Number] & ".PDF"
    
       DoCmd.OutputTo acOutputReport, sReport, acFormatPDF, sFile
    
   
    
       DoCmd.Close acReport, sReport
    
       rs.MoveNext

    Loop

The above would thus output a file with the job number, say like this:

1345.PDF

However, if you also want the date? Then you need/want/should/have to decide on what date format you want?

And you ALSO have to decide if you want spaces in the file name. In most cases, spaces in a file name are ok, but once again, often file names with spaces can trip up the OS.

However, let's assume you want today’s date, and you want this format:

  Job Number + Date + .PDF

So, then our code becomes this:

sFile = sPath & rs![Job_Number] & " " & format(date(), "YYYY-MM-DD") & ".PDF"

Now, above will place the job number first, then the date. And you may well want the date first (so it can sort correctly in a folder by date, and THEN it will sort by job numbers for that day).

So, with above, assuming a job number of 123456, then the above will result in:

123456 2025-11-17.PDF

However, one might prefer having the date first, and hence this:

2025-11-17 123456.PDF

So, it's not clear what format you want, but for the date first, then you have this:

sFile = sPath & format(date(), "YYYY-MM-DD") & " " & rs![Job Number] & ".PDF"

As noted, since you used a space in the column name?

Then in code, then when writing SQL, then when using that column, you forevermore MUST surround that column name with [].