Results 1 to 3 of 3
  1. #1
    tellis is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    2

    How to code date formats MM-YYYY and YYYYMM in a file name.

    Hello All,


    Is there a method to code a macro or module in access to automatically update components of a file name and Directory to the previous month date?

    HERE'S MY CODE:



    ext acExportDelim, "Export Specs_FWB-FHL Summary", "SelQry FHLSummary_CHAMPEI", "M:\Cargo Group\MIP\MIP Reports\06-2010\CHAMP_EI201006FHLSUM.CSV", True, ""

    The example above is for June reports which is processed in July. The data in red needs to be updated automatically to the previous month date using this format: MM-YYYY and YYYYMM. In my current process I am manually updating about 60 queries in the macro module at the beginning of each month. My plan is to eliminate this step by automating the naming process.

    Please note I have minimal experience with VB Code and the modules in Access, I used the 'Convert Macro to Code' feature to obtain the vb code.

    Any help in resolving this issue is much appreciated.

  2. #2
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    I don't quite follow your post. Are you saying that you manually change the date criteria in 60 separate queries?

    Your post initially seems like a formatting question, then is about the file path - but the above issue is much bigger than either in terms of time consumption one would think.

  3. #3
    tellis is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    2
    I created 4 sets of TransferText macros with 15 queries each (total 60). In the MACROS in the FILE NAME field, I manually change the date value to reflect the previous month date. This is a temporary fix until I can find a automatic solution to update the file name. See example below:

    BEFORE:
    M:\Cargo Group\MIP\MIP Reports\07-2010\CHAMP_EI201007FHLSUM.CSV

    AFTER:
    M:\Cargo Group\MIP\MIP Reports\08-2010\CHAMP_EI201008FHLSUM.CSV

    What I need to know is if there's coding that will automatically update the file name to the reporting date which is the previous month.

    I hope this clarifies my request.

    Thanks for your response.

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

Similar Threads

  1. How do you file save an 'Attachment' content through code?
    By morespamforya in forum Programming
    Replies: 3
    Last Post: 08-06-2010, 08:58 AM
  2. Queries that Check field formats
    By mojers in forum Queries
    Replies: 3
    Last Post: 05-02-2010, 07:39 AM
  3. Null Date control code problem
    By DanW in forum Forms
    Replies: 2
    Last Post: 11-10-2009, 03:13 PM
  4. Convert to MMM/YYYY
    By Brian62 in forum Queries
    Replies: 10
    Last Post: 08-31-2009, 04:54 PM
  5. Replies: 1
    Last Post: 06-11-2009, 05:40 PM

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