Results 1 to 14 of 14
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Export a tab delim text file from a query (Access 97 / SR2)

    I have a problem where I am trying to export to a tab delimited text file.

    Any currency fields end up with a $ at the front of the field. What I would like to do is exclude the $ sign.

    How can I do this?
    TIA
    Pat

  2. #2
    Lounger
    Join Date
    Apr 2001
    Location
    Khon Kaen, Thailand
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export a tab delim text file from a query (Access 97 / SR2)

    Just change the Format property of the field in Query to Standard. Click the currency field, go to View menu>Properties> select Standard in Format property.

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

    Re: Export a tab delim text file from a query (Access 97 / SR2)

    Thanks for your response, but I tried that and no good, it still has the $ sign in the text file.
    Pat

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

    Re: Export a tab delim text file from a query (Access 97 / SR2)

    You'll have to use the format funvction to change the format of the currency field in the export, which means you have to use a query if you aren't already.
    Charlotte

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Export a tab delim text file from a query (Access 97 / SR2)

    In your query, put the following in the fields that are currency. (Put it in the field row of the query.) This will drop off the $ before the export. This will work even if the format of the field is currency.

    Right([Field1],(Len([field1]-1)))


    Note: Response Edited
    HTH
    Regards,

    Gary
    (It's been a while!)

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Export a tab delim text file from a query (Access 97 / SR2)

    Note: If you change the format of the data field to a string, you will then export quotation marks around the fields as well as some null values. This may not be any better then the $ being exported. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,

    Gary
    (It's been a while!)

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

    Re: Export a tab delim text file from a query (Access 97 / SR2)

    I created a query using following expression for currency field ("AMT") using Val function:

    <pre>DOLLARS: Val([AMT])</pre>


    For format specified "Standard" and 2 decimal places. Exported to tab-delimited text file. Results looked like this:

    <pre>200.00
    100.00
    85.99
    10000.00
    99.99</pre>

    PS - Exported same query to Excel and numerical format was preserved, along with the decimal places.

    HTH

  8. #8
    Lounger
    Join Date
    Apr 2001
    Location
    Khon Kaen, Thailand
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export a tab delim text file from a query (Access 97 / SR2)

    Tried Format() and it worked out for me.

    ConCurr: Format([MyCurr],"Standard")

    This, I guess, forced the value to Standard format. The previous suggestion only hid the currency format but the currency still there.

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

    Re: Export a tab delim text file from a query (Access 97 / SR2)

    Thanks for your responses.
    It won't be outputting quotation marks because it's going to a Tab delimited file.

    I do not have the option of outputting the $ sign as this is an interface file into another system which does not want the $ signs.

    Pat

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

    Re: Export a tab delim text file from a query (Access 97 / SR2)

    Charlotte, I am using a query to output to the tab delimited file.

    Tim K. It didn't work for me.
    I tried what you said but that pesky $ sign is still. It looks good if you run the query but as soon as I run the TransferText command it reverts back to it's old ways and outputs the $ sign.
    I also use a specification name, this was necessary to get rid of the quotes and enforce the delimiter to a Tab delimiter.
    Pat <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

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

    Re: Export a tab delim text file from a query (Access 97 / SR2)

    I tried your idea and that little varmint ($ sign) is still there.
    It seems to take the $ sign from the table definition which are currency fields, and not from the query.

    Mark, would you send me a sample of what you are doing, I may have something subtle incorrect.

    Anybody else?
    Pat <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

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

    Re: Export a tab delim text file from a query (Access 97 / SR2)

    Got it !!!

    Mark's idea was the solution, however, what kept zapping me was that the Specification was grabbing me every time.
    What I found I had to do was to delete the Specification and recreate a new one after having changed the query. Obviously the Specification holds the formatting that it originally gets from the Query. So it doesn't matter what you change in the query it will take the formatting from the Specification.

    Thanks all for your help on this one. A lesson learned on Specifications.

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Export a tab delim text file from a query (Acc

    You mean a saved Export Specification? I tested this with a quick export to text format, with no specification, so had no problem. I do use saved Import & Export specifications for working with text files so this is good to know, I'll have to watch out for this potential pitfall.

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

    Re: Export a tab delim text file from a query (Acc

    It's amazing how you can go round and around in circles. It was not obvious as to what was the problem.
    So the rule should be if you are using an Export Specification remember to generate it again after changing the source of the export !!!

    Thanks for your help.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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