export more than 2 decimal places as text

[Marsha]

I need to know why access won't export more than two
decimal places as text? If I export to a datasheet - I
get all the data if I export as text, it will seems to
round to dec places....

 

[Ken Snell [MVP]]

Create a query that will export the data. In place of the field that you
want to export with specific decimal places, use a calculated field similar
to this (example shows 4 decimal places):

OutputField: Format([FieldName], "0.0000")

Then export the query to the text file.

 

[David Seeto via AccessMonster.com]

I'm experiencing the same problem, although it's actually truncating the output at two decimal places rather than rounding. If, however, the resulting number would be .00, Access gives the number in scientific notation, just as the nearby post "Exporting to CSV" file says. The format seems to work, but by default Access will put quotes around all text fields, which this has now become.

To work around this, I am telling Access not put put quotes around text and putting quotes around all text fields manually in the query ie. with """" & [field name] & """", but I would dearly love to understand why Access is behaving this way. I haven't been able to find anything on the Microsoft message boards, and Google came up with this forum...

*****************************************
* A copy of the whole thread can be found at:
* http://www.accessmonster.com/Uwe/Forum.aspx/access-externaldata/6994
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abuse.aspx?aid=aa5d12e3f5cf4e56b2074b37b1b0fc56
*****************************************

 

[Ken Snell [MVP]]

Set up an export specification that specifies that text values are not to be
delimited by " characters. Then use that export spec in a macro
(TransferText) or VBA code (DoCmd.TransferText).

You set up your spec by starting to do the export manually, and when the
wizard window opens, click on the Advanced button at bottom left -- you then
can set all the export specification parameters and save the spec as
whatever name you want. Then cancel the export and use the spec name in one
of the above methods.

What you've done to reformat output can be used for specifiying the decimal
format of the field's values. You create calculated fields in the query that
use the Format function to "convert" the output to your desired format.
--

Ken Snell
<MS ACCESS MVP>

I'm experiencing the same problem, although it's actually truncating the

output at two decimal places rather than rounding. If, however, the
resulting number would be .00, Access gives the number in scientific
notation, just as the nearby post "Exporting to CSV" file says. The format
seems to work, but by default Access will put quotes around all text fields,
which this has now become.

To work around this, I am telling Access not put put quotes around text

and putting quotes around all text fields manually in the query ie. with
"""" & [field name] & """", but I would dearly love to understand why Access
is behaving this way. I haven't been able to find anything on the Microsoft
message boards, and Google came up with this forum...

 

[david epsom dot com dot au]

In an export to text, the number of decimal places is
controled by the 'regional settings' in the control
panel on your PC.

(david)

 

[David Seeto via AccessMonster.com]

Yes, as I said, I found this answer on these forums after I had posted my initial question. I have tested it and it works. I was just wondering whether there was a FAQ or something somwehere where it could be noted and preserved, since I could not find any acknowledgement of it anywhere the Microsoft web site - even their Access 2000 support article on this very topic only gives Ken's initial solution of reformatting the field and not the actual underlying reason of the Regional Settings.

*****************************************
* A copy of the whole thread can be found at:
* http://www.accessmonster.com/Uwe/Forum.aspx/access-externaldata/6994
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abuse.aspx?aid=1064712c134642ab8d606f9020fe2b15
*****************************************

 

[david epsom dot com dot au]

wondering whether there was a FAQ or something somewhere

I think that it is in the help if you know where to look: at
least it is in the Web version of the A2003 help (near the
bottom, under 'NumberDigits')
http://office.microsoft.com/en-us/assistance/HP010321661033.aspx

You will want to read this section anyway, because it is
relevant to what you are trying to do.

(david)

Yes, as I said, I found this answer on these forums after I had posted my

initial question. I have tested it and it works. I was just wondering
whether there was a FAQ or something somwehere where it could be noted and
preserved, since I could not find any acknowledgement of it anywhere the
Microsoft web site - even their Access 2000 support article on this very
topic only gives Ken's initial solution of reformatting the field and not
the actual underlying reason of the Regional Settings.