Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    I am trying to export a query to a fixed width file. I also tried csv with the same results.

    The problem is the first field is 000015452. When exporting it drops the leading zeros. I have tried Format([myfield],"000000000"), I tried converting the field to a text field, among other things. When viewing the query the zeros are there. But when viewing the text file, the zeros have been dropped.

    What am I overlooking?

    Thanks in advance for any ideas and help.

    Ken

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Exporting ignores the format set for a numeric column; you must use the Format function or a text field.

    When I export from Access 2002 to a text file, leading zeros created by the Format function or in a text field are preserved.

    Which application are you using to view the exported file? If I open such a file in Excel, it tries to interpret a column as numeric if possible, so I lose the leading zeros. But if I use Notepad, I do see them.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='796122' date='02-Oct-2009 17:21']Exporting ignores the format set for a numeric column; you must use the Format function or a text field.

    When I export from Access 2002 to a text file, leading zeros created by the Format function or in a text field are preserved.

    Which application are you using to view the exported file? If I open such a file in Excel, it tries to interpret a column as numeric if possible, so I lose the leading zeros. But if I use Notepad, I do see them.[/quote]
    Obviously I am doing something wrong then, as I use Notepad to open. I am using Access 2003, and I tried both the Format function as well as exporting as a text field. I even tried hard-coding the data, instead of using the field in the table. The following SQL Statement is an example of the query.
    SELECT Format(23396,"000000000") AS ID, tmpTEST.ICN, tmpTEST.HICN, tmpTEST.I_txtPersonID, tmpTEST.I_txtLastName, tmpTEST.I_txtFirstName, tmpTEST.Gender, tmpTEST.DOB
    FROM tmpTEST;

    Yet the results drop the leading zeros in the first field.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'm sorry, I can't explain that.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='kwvh' post='796119' date='03-Oct-2009 10:10']I am trying to export a query to a fixed width file. I also tried csv with the same results.

    The problem is the first field is 000015452. When exporting it drops the leading zeros. I have tried Format([myfield],"000000000"), I tried converting the field to a text field, among other things. When viewing the query the zeros are there. But when viewing the text file, the zeros have been dropped.

    What am I overlooking?

    Thanks in advance for any ideas and help.

    Ken[/quote]
    What command do you use to export, OutputTo or TransferText, or maybe some other way?

  6. #6
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    [quote name='patt' post='796136' date='02-Oct-2009 17:57']What command do you use to export, OutputTo or TransferText, or maybe some other way?[/quote]
    Right now I am in test mode, so I am not exporting via VBA, but instead export the query manually (Export/Save As Type/Text Files (*.txtl;*.csv;*.tab;*.asc), give it a name. When I exported delimited I set the field widths.

  7. #7
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    [quote name='patt' post='796136' date='02-Oct-2009 17:57']What command do you use to export, OutputTo or TransferText, or maybe some other way?[/quote]
    New discovery. If I move the field from the first position in the query to the second, it DOES leave the zeros in.

    Weird.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='kwvh' post='796143' date='03-Oct-2009 11:00']New discovery. If I move the field from the first position in the query to the second, it DOES leave the zeros in.

    Weird.[/quote]
    Must remember that one lol

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Try changing your query to this:
    Code:
    SELECT CStr(Format(23396,"000000000")) AS ID, tmpTEST.ICN, tmpTEST.HICN, tmpTEST.I_txtPersonID, tmpTEST.I_txtLastName, tmpTEST.I_txtFirstName, tmpTEST.Gender, tmpTEST.DOB
    FROM tmpTEST;
    I have encountered several strange issues with the text export function in Access - Yes/No fields are likely to be problematic, as are date fields - but in many cases changing them to Text with the CStr function seems to resolve them. Note however that if you use it on a Null field value, it causes an error.
    Wendell

Posting Permissions

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