Adding system date in report file name (1 Viewer)

AnilBagga

Member
Local time
Today, 15:04
Joined
Apr 9, 2020
Messages
104
I have a main form in the DB where I have several command buttons for reports. I save these Excel and PDF reports in a common folder as seen below

Private Sub Command57_Click()
DoCmd.OutputTo acOutputReport, "rptContainersInTransit", acFormatPDF, "C:\Access\ContainersTransit.pdf"
End Sub

Private Sub Command58_Click()
DoCmd.OutputTo acOutputQuery, "qryContainersInTransit", acFormatXLSX, "C:\Access\ContainersTransit.xlsx"
End Sub

The new files naturally overwrites the previous file.

Can I add a code such that the file name has a system date in it and and a suffix 0,1,2 is added as shown below

ContainersTransit-08102020-0

We use the DDMMYYYY format for dates and -0 is the first file of this date

Is this desire too much to ask from Access?
 

arnelgp

error reading drive A:
Local time
Today, 17:34
Joined
May 7, 2009
Messages
10,844
create a function and use it in your DoCmd.OutputTo.
paste this on a Module:
Code:
Public Function fncSerialFile(ByVal strFilePath As String) As String
'
' arnelgp
'
' strFilePath is the "path" + "filename.ext"
' example:
'
' "d:\ContainersTransit.xlsx" or
' "d:\ContainersTransit.pdf"
'
'
' returns:
'
' "d:\ContainersTransit-DDMMYYYY-X.xlsx"
' "d:\ContainersTransit-DDMMYYYY-X.pdf"
'
    Dim strNewFile As String
    Dim strPath As String
    Dim strFile As String
    Dim strExt As String
    Dim intX As Integer
    strPath = fncFilePath(strFilePath)
    strExt = fncFileExtension(strFilePath)
    strFile = Replace(strFilePath, strPath, vbNullString)
    strFile = Replace(strFile, strExt, vbNullString)
    strNewFile = strPath & strFile & "-" & Format(Date, "DDMMYYYY") & "-" & intX & strExt
    Do Until Len(Dir(strNewFile)) = 0
        intX = intX + 1
        strNewFile = strPath & strFile & "-" & Format(Date, "DDMMYYYY") & "-" & intX & strExt
    Loop
    fncSerialFile = strNewFile
End Function

Public Function fncFilePath(ByVal strFile As String) As String
    ' return path with backslash
    Dim intX As Integer
    intX = InStrRev(strFile, "\")
    If intX > 0 Then
        fncFilePath = Left$(strFile, intX)
    End If
End Function

Public Function fncFileExtension(ByVal strFile As String)
    Dim intX As Integer
    intX = InStrRev(strFile, ".")
    If intX > 0 Then
        fncFileExtension = Mid(strFile, intX)
    End If
End Function
your code will need to change to:
Code:
Private Sub Command57_Click()
DoCmd.OutputTo acOutputReport, "rptContainersInTransit", acFormatPDF, fncSerialFile("C:\Access\ContainersTransit.pdf")
End Sub

Private Sub Command58_Click()
DoCmd.OutputTo acOutputQuery, "qryContainersInTransit", acFormatXLSX, fncSerialFile("C:\Access\ContainersTransit.xlsx")
End Sub
 

AnilBagga

Member
Local time
Today, 15:04
Joined
Apr 9, 2020
Messages
104
create a function and use it in your DoCmd.OutputTo.
paste this on a Module:
Code:
Public Function fncSerialFile(ByVal strFilePath As String) As String
'
' arnelgp
'
' strFilePath is the "path" + "filename.ext"
' example:
'
' "d:\ContainersTransit.xlsx" or
' "d:\ContainersTransit.pdf"
'
'
' returns:
'
' "d:\ContainersTransit-DDMMYYYY-X.xlsx"
' "d:\ContainersTransit-DDMMYYYY-X.pdf"
'
    Dim strNewFile As String
    Dim strPath As String
    Dim strFile As String
    Dim strExt As String
    Dim intX As Integer
    strPath = fncFilePath(strFilePath)
    strExt = fncFileExtension(strFilePath)
    strFile = Replace(strFilePath, strPath, vbNullString)
    strFile = Replace(strFile, strExt, vbNullString)
    strNewFile = strPath & strFile & "-" & Format(Date, "DDMMYYYY") & "-" & intX & strExt
    Do Until Len(Dir(strNewFile)) = 0
        intX = intX + 1
        strNewFile = strPath & strFile & "-" & Format(Date, "DDMMYYYY") & "-" & intX & strExt
    Loop
    fncSerialFile = strNewFile
End Function

Public Function fncFilePath(ByVal strFile As String) As String
    ' return path with backslash
    Dim intX As Integer
    intX = InStrRev(strFile, "\")
    If intX > 0 Then
        fncFilePath = Left$(strFile, intX)
    End If
End Function

Public Function fncFileExtension(ByVal strFile As String)
    Dim intX As Integer
    intX = InStrRev(strFile, ".")
    If intX > 0 Then
        fncFileExtension = Mid(strFile, intX)
    End If
End Function
your code will need to change to:
Code:
Private Sub Command57_Click()
DoCmd.OutputTo acOutputReport, "rptContainersInTransit", acFormatPDF, fncSerialFile("C:\Access\ContainersTransit.pdf")
End Sub

Private Sub Command58_Click()
DoCmd.OutputTo acOutputQuery, "qryContainersInTransit", acFormatXLSX, fncSerialFile("C:\Access\ContainersTransit.xlsx")
End Sub
WOW!!

This is Super!

Couple of queries. My queries may seem dumb as I dont really follow the code fully and will just paste them

1. The Public Function fncSerialFile and fncFilePath will have to be copied in the "OnClick" Event of each report button? Perhaps not. If not, where do I place it? See the screenshot of the page where I have all these commands

2. In the code DoCmd.OutputTo acOutputReport, "rptContainersInTransit", acFormatPDF, fncSerialFile("C:\Access\ContainersTransit.pdf"), do copy exactly like this , including the text in brackets?
 

Attachments

  • Screenshot (2).png
    Screenshot (2).png
    167.7 KB · Views: 12

arnelgp

error reading drive A:
Local time
Today, 17:34
Joined
May 7, 2009
Messages
10,844
1. goto VBA (Alt-F11). on the menu->Insert->Module. paste the code and save.
2. exactly like i posted it.
 

AnilBagga

Member
Local time
Today, 15:04
Joined
Apr 9, 2020
Messages
104
Arnel

I finally got around to implementing it and after an initial hiccup, found the way to implement it. I really appreciate your effort

Regards,
Anil
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:34
Joined
Feb 19, 2002
Messages
30,059
Unless you want files dated the first day of a month to always sort first, it is far more useful to use yyyymmdd as your string date format.
 

Users who are viewing this thread

Top Bottom