Results 1 to 13 of 13
  1. #1
    jaeapla is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Location
    Atlanta, GA
    Posts
    12

    Adding a range of dates to a PDF file name generated from Access VBA

    I can't believe this is an unusual request, but all I can find is how to add today's date to a file name. I have a report that encompasses a range of dates (dStart and dEnd) which I enter from the keyboard into my query. I can then generate a report as well as a PDF that reflect both of these fields in the report heading. My problem is that I can't insert these fields into the filename of the report (e.g., All FCREA Paid Members between 01/01/2023 and 04/25/2023.pdf). I have tried what I considered the logical ways in the VBA module, like concatenating them into the filename e.g.,


    Dim dStart As String
    Dim dEnd As String
    Dim stDocName As String
    Dim EndDate1 As String


    EndDate1 = dStart & " and " & dEnd


    strPath = "C:\Users\MyName\OneDrive\Documents\FCREA"
    strFileName = "All FCREA Paid Members between " & EndDate1 & ".pdf"
    stDocName = "rptSpecificDateRangePayments"


    DoCmd.OpenReport stDocName, acViewReport, , "", acNormal
    DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, strPath & strFileName, True, "", , acExportQualityPrint
    DoCmd.Close stDocName


    The report displays properly on the screen, but the PDF file fails to generate, and I get the message "The OutputTo action was canceled"


    I would greatly appreciate any help I can get -- I feel like I am missing something simple.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    On my phone but check your file name does not include characters that are not allowed - such as / and \ which are typically used in dates

    also you don’t seem to be assigning a value to dstart or dend

    and your strpath seems to be missing a \ at the end

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    e.g., All FCREA Paid Members between 01/01/2023 and 04/25/2023.pdf
    You can't have backslashes in a file name.

    EDIT - or forward either. Dang, I wish the forum would provide notices of posts when you're reading/composing. Late again.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    jaeapla is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Location
    Atlanta, GA
    Posts
    12
    1. I use dashes in my dates -- which I believe are OK
    2. My bad - when I copied the path over, I missed the last backslash, but it is there
    strPath = "C:\Users\MyName\OneDrive\Documents\FCREA"
    3. where they are assigned is I believe what the problem is, as they print out in the report in the heading but they don't get picked up in the file same.

    Thanks for the quick response.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Post code between CODE tags and forum will not drop ending \ character from path string. Or go to Advanced editor and uncheck "Automatically parse links in text" before submitting post. Note that it was also dropped in post #4.

    if you want files to sort in folder by filename and dates are part of name, use yyyy-mm-dd structure.

    I don't use dynamic parameterized query, certainly not popup input prompts. Your code would not know what you input into query prompts. Input info to textboxes on form and query or report textboxes references those controls. Your code to output to PDF can also reference those controls. Where is this code run from?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    jaeapla is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Location
    Atlanta, GA
    Posts
    12

    I think I need to clarify what I tried to explain in my Post "Adding a range of dates to a PDF file"

    Quote Originally Posted by jaeapla View Post
    1. I use dashes in my dates -- which I believe are OK
    2. My bad - when I copied the path over, I missed the last backslash, but it is there
    strPath = "C:\Users\MyName\OneDrive\Documents\FCREA"
    3. where they are assigned is I believe what the problem is, as they print out in the report in the heading but they don't get picked up in the file same.
    Thanks for the quick response.
    ************************************************** **********************************************
    *****************************MY RESPONSE TO WHAT I HAVE READ SO FAR****************************** ************************************************** **********************************************
    I THINK THAT I HAVE NOT PRESENTED MY PROBLEM CLEARLY ENOUGH AS ORIGINALLY STATED:
    i HAVE A DATABASE MADE UP OF MEMBERS OF A NON-PROFIT ORGANIZATION ASSOCIATED WITH A LARGE ORGANIZATION. They pay dues and I keep up with their payments among other things. We have several management reports that we generate showing dues receipts for a period of time. The report generates fine, but when I output a PDF file of this report, I can't get the dates of the report (start and stop dates for the given report) into the PDF filename I am generating. I want the filename to be like "FCREA Paid Members from 01-01-2023 to 06-30-2023.pdf", but I can't figure out how to get those dates in the filename (other than manually).I execute a REPORT which has a QUERY as its input. I get two pop-ups during the QUERY portion where I enter those start and stop dates (and these are the dates I am looking later on to insert them when I output my PDF output file). So in simplist terms, I want those two dates to appear as part of the PDF file name (as demonstrated above). By the way, those two dates work perfectly on the heading of the internal REPORT I get from the data, but I need the PDF version to properly be distributed. Again, thanks in advance for any help I can get.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I think we understand the problem and have offered resolution.
    You have not responded to my comments in post #5.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    also hasn't responded to my second point. But seems to me the solution suggested by June to use a form to enter the dates is the solution. Modify your query to get the parameters from the form or modify your openreport to include the values as a filter

    If you really want to set this data on the fly and unvalidated then use input boxes to capture the data - something like this air code (not tested)

    Code:
    Dim dStart As String
    Dim dEnd As String
    Dim stDocName As String
    Dim EndDate1 As String
    
    dstart=inputbox("Enter Start Date")
    dend=inputbox("Enter End Date")
    EndDate1 = dstart & " and " & dend
    
    strPath = "C:\Users\MyName\OneDrive\Documents\FCREA\"
    strFileName = "All FCREA Paid Members between " & EndDate1 & ".pdf"
    stDocName = "rptSpecificDateRangePayments"
    
    
    DoCmd.OpenReport stDocName, acViewReport, , "Between #" & dstart & "# AND #" & dend & "#", acNormal
    DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, strPath & strFileName, True, "", , acExportQualityPrint
    DoCmd.Close stDocName

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Code:
    pdfFilename = "FCREA Paid Members from " & Format(StartDate,"mm-dd-yyyy") &  " to " & Format(EndDate,"mm-dd-yyyy") & ".pdf"
    Debug.Print pdfFilename
    
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    jaeapla is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Location
    Atlanta, GA
    Posts
    12
    Quote Originally Posted by June7 View Post
    I think we understand the problem and have offered resolution.
    You have not responded to my comments in post #5.
    Sorry, I didn't know I needed to reply to every suggestion I get. A later post solved my problem using the FORMAT function.

  11. #11
    jaeapla is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Location
    Atlanta, GA
    Posts
    12

    Problem Solved

    Quote Originally Posted by Welshgasman View Post
    Code:
    pdfFilename = "FCREA Paid Members from " & Format(StartDate,"mm-dd-yyyy") &  " to " & Format(EndDate,"mm-dd-yyyy") & ".pdf"
    Debug.Print pdfFilename
    
    Thanks so much -- this solved my problem perfectly.

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I didn't know I needed to reply to every suggestion I get
    And we know that we don't have to offer suggestions to every question, and that's the group you will find yourself in if that is going to be your approach. People devote their free time to help others and no one gets paid for that. Acknowledgement goes a long way as does coming back to post a solution that you found by yourself if that's what you mean.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    jaeapla is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Location
    Atlanta, GA
    Posts
    12
    You are right -- I apologize, and I did get the solution from one of the people that answered my post.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 21
    Last Post: 06-23-2020, 11:45 AM
  2. Replies: 5
    Last Post: 10-02-2017, 02:02 PM
  3. Replies: 2
    Last Post: 11-14-2012, 08:32 AM
  4. Query input dates for range of dates in two fields
    By kagoodwin13 in forum Queries
    Replies: 3
    Last Post: 02-13-2012, 04:25 PM
  5. Replies: 3
    Last Post: 09-29-2009, 07:08 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums