Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Location
    Tucson, Arizona, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delimited Text Date Format (Access 2000 SP3)

    Every quarter we need to upload a delimited text file generated from a parameter query (based on the date field) using the | separator to an online reporting site. They take our records and merge them into a database at their end that requires a MM/DD/YYYY format for date fields. Using Access 2000's user interface menu items ( File > Export... ) will fire up a wizard to produce the needed delimited text file by choosing "Text Files (*.txt;*.csv;*.tab;*.asc)" from the "Save as type:" dropdown menu. However, the date field format changes in the resulting .txt file. The Date/Time field setting for the field in question is set both in Windows 2000 (SP-4) Regional Options and in Access 2000's (SP-3) table properties to 'Short Date' (i.e.; M/d/yyyy) and this is all that appears in the user interface when viewing records in the database. However, when creating a delimited file, it automatically adds the time (H:mm:ss) too. The resulting .txt field will show:

    |8/28/2003 0:00:00|

    The online database that we upload to will not accept records that do not match their date format. When I delete the " 0:00:00", everything uploads fine. Do you know of some way to successfully produce such a delimited .txt file using VBA that can control Access' default date output format? Or is there some easier fix for the resulting .txt file produced by the wizard?

    Thanks for your consideration of this dilemma!
    Brady

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delimited Text Date Format (Access 2000 SP3)

    The usual advice is to create a query with a calculated field that formats the text:

    Format([MyDateField],"mm/dd/yyyy")

    By default, the dates will be enclosed in quotes since the calculated field is a text field; there is a setting for this in the Export Text Wizard.

  3. #3
    New Lounger
    Join Date
    Oct 2003
    Location
    Tucson, Arizona, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delimited Text Date Format (Access 2000 SP3)

    Excellent! Thank you Hans

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •