Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    Sylacauga, Alabama, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exporting Date Field to Txt File (97)

    Using Access 97 to format a space-delimited file to import into AS400 file. The "string" for each row is beyond the limits of Excel to format as a prn file. When I export to space-delimited text file from Access the date (which I want to be YYYYMMDD)exports as mm/dd/yyyy 0:00:00. How can I configure Access to export the date correctly?

    Also, I have another field that is 4 characters, a number field that must have leading zeroes in the final space-delimited file. I can't configure it to keep the leading zeroes.

    Have a short timeframe to figure this out and would appreciate any help available.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Exporting Date Field to Txt File (97)

    Firstly use a query so that you can then use the format command.
    The format command for the date could be FormatYourDateField,"yyyymmdd") and for your other requirement just lookup the format command in the help.
    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Exporting Date Field to Txt File (97)

    Your number field will have to formatted as text and padded with leading zeros in the query in order to get them into the text file. In 97, there isn't any automatica way to do this, but code has been posted in this forum numerous times for formatting with leading zeros and left padding numeric strings. I'd suggest a Lounge Search for those topics.

    Access is exporting the date correctly, it just isn't exporting it the way you want. What is coming out is date and time in the system format, so again, you'll have to use the Format([DateField], "yyyymmdd") function in your query to return only the short date in the format you wish.
    Charlotte

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Exporting Date Field to Txt File (97)

    Here is example of query SQL to export to text file in desired format., using Format function as previously suggested:

    <pre>SELECT Format([RecordID],"0000") AS RecordNo,
    Format([DateFld],"yyyymmdd") AS RecordDate
    FROM TABLE1;</pre>


    In example RecordID is numeric field (Long), DateFld is date field (Date/Time). Use "0000" as number format to insert leading zeroes. When exported to space-delimited text file resulting data looked like this:

    <pre>"0001" "20020101"
    "0002" "20001225"
    "0003" "19990622" </pre>


    HTH

  5. #5
    New Lounger
    Join Date
    Sep 2002
    Location
    Sylacauga, Alabama, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting Date Field to Txt File (97)

    Wonderful! I knew it was something simple, but kept messing in the table design instead of a query. Thanks to all who responded.

Posting Permissions

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