Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    Houston TX
    Posts
    23

    Question Unanswered: Currency Export as Fixed length - Killing me! Help Help

    I am trying to export a qry as a fixed length flat file. One of the fields is a currency field, what i want to make it do is a fixed 12 character long field with no decimals in there.
    What i have done so far is to define it as long integer, used format comand in qry as
    Exp1: Format ([mytable],"000000000000")
    this basically cures the problem tempraily, as if you look at the data inside access it will show the amount $610.00 as 000000061000, but as soon as the export is done the text file is not what it is supposed to be. In the text file the amout shows right back as $610.00

    Any help is appriciated.

    P.S i am using the export specs and wizard to do the export as fixed length from access 2000.
    Believe Everybody Trust Nobody

  2. #2
    Join Date
    Feb 2004
    Location
    UK
    Posts
    17
    Assuming you are exporting to excel you could use the =Text() function in Excel.

    Eg exported value of $610 in cell A1 could be formated as 000000061000
    using =TEXT(A1,"0000000000")&"00" in another column

    Hope this helps

  3. #3
    Join Date
    Jan 2004
    Location
    Kennesaw, Ga
    Posts
    20
    SELECT Format(CLng([Table2].[amount]*100),"000000000000") AS Expr1
    FROM Table2;

  4. #4
    Join Date
    Feb 2004
    Location
    Houston TX
    Posts
    23

    Unhappy Still not working

    tried the CLng option and it doesnt work (it works to the point that it shows the "00000000000000" in access, but as soon as the text file is made , there is not data again,

    is this a bug or some thing ? i am totally confused .
    Believe Everybody Trust Nobody

  5. #5
    Join Date
    Jan 2004
    Location
    Kennesaw, Ga
    Posts
    20
    click the advanced button on the export text wizard and define the field information.

  6. #6
    Join Date
    Feb 2004
    Location
    Houston TX
    Posts
    23

    Thumbs down

    after doing the advance button, i even saved a setting and then closed everything, opened the db again loaded the specs (upto this poing no problem). Start to export, in the preview area of access, all the zero show up (still no problem). Press ok to save the text file BIG Problem. The numbers start showing up as $610.00
    Believe Everybody Trust Nobody

  7. #7
    Join Date
    Sep 2004
    Posts
    1
    I was having a similar problem with exporting to a fixed-width text file, and it was driving me crazy. I traced the problem to a hidden Data Type field in my export specification that was overriding every parameters I had set in Table Design and Query Design during troubleshooting.

    For some bizarre reason, Access's Export Text Wizard hides the Data Type field by default. The only columns you see are Field Name, Start and Width. To display Data Type, hover the mouse between Field Name and Start, and then double-click.

    To preserve leading zeros in your field, change the Data Type to Text.

    I hope this helps.

Posting Permissions

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