Results 1 to 3 of 3
  1. #1
    Sephaerius is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2013
    Posts
    62

    VBA to append date entered from a Form field

    I feel like this is pretty simple, but in searching I cannot seem to find the precise answer I'm looking for.


    What I have is an Access form with a textbox that the user will enter a date into, formatted "3/30/2017".
    I have VBA that exports a table using DoCmd.TransferSpreadsheet, and on the Name parameter I'm setting it to tack the date onto the end of the exported file name, such as:
    Environ("USERPROFILE") & "\Desktop\filename" & Format(Date, "mmddyy") & ".xlsb"

    This works, but ideally I'd like the date that was entered onto my Form to take the place of & Format(Date, "mmddyy") &

    What's the proper syntax to use to call that form field and format it on the end of my file name in the same format of "mmddyy"?

    Thanks!

  2. #2
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    If the DoCmd.TransferSpreadsheet statement is within the same form, then replace "Date" with Me.NameOfYourControlHoldingValue.
    If it is outside of the form then change "Date" to "Forms!NameOfYourForm!NameOfControlHoldingValu e.

  3. #3
    Sephaerius is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2013
    Posts
    62
    It was outside the Form, and that worked perfectly! I knew it was something simple. Thanks for your help!

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

Similar Threads

  1. Replies: 1
    Last Post: 08-10-2016, 02:44 AM
  2. Replies: 3
    Last Post: 06-23-2015, 02:40 PM
  3. Replies: 7
    Last Post: 11-20-2014, 07:03 PM
  4. Replies: 1
    Last Post: 11-13-2014, 08:26 AM
  5. Replies: 8
    Last Post: 10-26-2012, 09:17 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