Export decimals to txt

E

Emily Edgington

I am using Access 2000 on Windows XP. I am trying to export a query to a txt
file while maintaining the decimal places on particular fields. I have 3
fields in my query for which I want more than 2 decimals places.

I have seen many postings on this issue, and have tried the solutions to no
avail. Someone suggested adjusting the regional and language settings in
control panel. This worked, except that all of my decimal fields exported
with 5 decimals places. No good.

I’ve tried the suggestions for forcing the formatting, using Format() and/or
CStr(). For some reason, that is not working for me. I still get the same 2
decimal place results no matter what I do. When I export to xls, I get all
of the decimals I specify.

Below is my query sql. Qty, UnitPrice, and ExtAmt are the 3 fields I am
struggling with. Can anyone help?

Thanks,
Emily

SELECT tblOutput.APBU, tblOutput.VndrID, tblOutput.AddrSeq,
tblOutput.VndrNm, tblOutput.VndrAddr, tblOutput.VndrCity, tblOutput.VndrSt,
tblOutput.VndrZip, tblOutput.BU, tblOutput.BUNm, tblOutput.InvDt,
tblOutput.InvID, tblOutput.DelDt, tblOutput.OrdDt, tblOutput.Total,
tblOutput.Tax, tblOutput.Freight, tblOutput.DelAddr, tblOutput.DelCity,
tblOutput.DelSt, tblOutput.DelZip, CStr([Qty]) AS Qty4, tblOutput.ItemDescr,
tblOutput.UOM, Format(CStr([UnitPrice]),"0.00000") AS UnitPrice5,
CStr([ExtAmt]) AS ExtAmt3, tblOutput.Acct, tblOutput.Dept, tblOutput.Prod,
tblOutput.Proj, tblOutput.Origin, tblOutput.PymntMess
FROM tblOutput
ORDER BY tblOutput.APBU, tblOutput.VndrID, tblOutput.AddrSeq,
tblOutput.InvDt, tblOutput.InvID;
 
K

Ken Snell MVP

How are you exporting the query's data? If by OutputTo, it overrides any
formatting that you put in the query. If by TransferText and you use an
Export Specification, the specification will overwrite any formatting that
you put in the query. If by File | Export, it will overwrite the formatting
that you put in the query (essentially similar to using OutputTo).
 
K

Ken Snell MVP

How are you exporting the query's data? If by OutputTo, it overrides any
formatting that you put in the query. If by TransferText and you use an
Export Specification, the specification will overwrite any formatting that
you put in the query. If by File | Export, it will overwrite the formatting
that you put in the query (essentially similar to using OutputTo).
 
E

Emily Edgington

Thank you for your quick response! I am using File - Export. What option do
I have that doesn't override the formatting?

Ken Snell MVP said:
How are you exporting the query's data? If by OutputTo, it overrides any
formatting that you put in the query. If by TransferText and you use an
Export Specification, the specification will overwrite any formatting that
you put in the query. If by File | Export, it will overwrite the formatting
that you put in the query (essentially similar to using OutputTo).

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Emily Edgington said:
I am using Access 2000 on Windows XP. I am trying to export a query to a
txt
file while maintaining the decimal places on particular fields. I have 3
fields in my query for which I want more than 2 decimals places.

I have seen many postings on this issue, and have tried the solutions to
no
avail. Someone suggested adjusting the regional and language settings in
control panel. This worked, except that all of my decimal fields exported
with 5 decimals places. No good.

I've tried the suggestions for forcing the formatting, using Format()
and/or
CStr(). For some reason, that is not working for me. I still get the
same 2
decimal place results no matter what I do. When I export to xls, I get
all
of the decimals I specify.

Below is my query sql. Qty, UnitPrice, and ExtAmt are the 3 fields I am
struggling with. Can anyone help?

Thanks,
Emily

SELECT tblOutput.APBU, tblOutput.VndrID, tblOutput.AddrSeq,
tblOutput.VndrNm, tblOutput.VndrAddr, tblOutput.VndrCity,
tblOutput.VndrSt,
tblOutput.VndrZip, tblOutput.BU, tblOutput.BUNm, tblOutput.InvDt,
tblOutput.InvID, tblOutput.DelDt, tblOutput.OrdDt, tblOutput.Total,
tblOutput.Tax, tblOutput.Freight, tblOutput.DelAddr, tblOutput.DelCity,
tblOutput.DelSt, tblOutput.DelZip, CStr([Qty]) AS Qty4,
tblOutput.ItemDescr,
tblOutput.UOM, Format(CStr([UnitPrice]),"0.00000") AS UnitPrice5,
CStr([ExtAmt]) AS ExtAmt3, tblOutput.Acct, tblOutput.Dept, tblOutput.Prod,
tblOutput.Proj, tblOutput.Origin, tblOutput.PymntMess
FROM tblOutput
ORDER BY tblOutput.APBU, tblOutput.VndrID, tblOutput.AddrSeq,
tblOutput.InvDt, tblOutput.InvID;
Click to expand...
Click to expand...
 
E

Emily Edgington

Thank you for your quick response! I am using File - Export. What option do
I have that doesn't override the formatting?

Ken Snell MVP said:
How are you exporting the query's data? If by OutputTo, it overrides any
formatting that you put in the query. If by TransferText and you use an
Export Specification, the specification will overwrite any formatting that
you put in the query. If by File | Export, it will overwrite the formatting
that you put in the query (essentially similar to using OutputTo).

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Emily Edgington said:
I am using Access 2000 on Windows XP. I am trying to export a query to a
txt
file while maintaining the decimal places on particular fields. I have 3
fields in my query for which I want more than 2 decimals places.

I have seen many postings on this issue, and have tried the solutions to
no
avail. Someone suggested adjusting the regional and language settings in
control panel. This worked, except that all of my decimal fields exported
with 5 decimals places. No good.

I've tried the suggestions for forcing the formatting, using Format()
and/or
CStr(). For some reason, that is not working for me. I still get the
same 2
decimal place results no matter what I do. When I export to xls, I get
all
of the decimals I specify.

Below is my query sql. Qty, UnitPrice, and ExtAmt are the 3 fields I am
struggling with. Can anyone help?

Thanks,
Emily

SELECT tblOutput.APBU, tblOutput.VndrID, tblOutput.AddrSeq,
tblOutput.VndrNm, tblOutput.VndrAddr, tblOutput.VndrCity,
tblOutput.VndrSt,
tblOutput.VndrZip, tblOutput.BU, tblOutput.BUNm, tblOutput.InvDt,
tblOutput.InvID, tblOutput.DelDt, tblOutput.OrdDt, tblOutput.Total,
tblOutput.Tax, tblOutput.Freight, tblOutput.DelAddr, tblOutput.DelCity,
tblOutput.DelSt, tblOutput.DelZip, CStr([Qty]) AS Qty4,
tblOutput.ItemDescr,
tblOutput.UOM, Format(CStr([UnitPrice]),"0.00000") AS UnitPrice5,
CStr([ExtAmt]) AS ExtAmt3, tblOutput.Acct, tblOutput.Dept, tblOutput.Prod,
tblOutput.Proj, tblOutput.Origin, tblOutput.PymntMess
FROM tblOutput
ORDER BY tblOutput.APBU, tblOutput.VndrID, tblOutput.AddrSeq,
tblOutput.InvDt, tblOutput.InvID;
Click to expand...
Click to expand...
 
K

Ken Snell MVP

Use a macro or VBA code to run TransferText action. Do not use an export
specification. If the number exports with " characters around it, then
you'll need to create an export specification that will not put " characters
around text values.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




Emily Edgington said:
Thank you for your quick response! I am using File - Export. What option
do
I have that doesn't override the formatting?

Ken Snell MVP said:
How are you exporting the query's data? If by OutputTo, it overrides any
formatting that you put in the query. If by TransferText and you use an
Export Specification, the specification will overwrite any formatting
that
you put in the query. If by File | Export, it will overwrite the
formatting
that you put in the query (essentially similar to using OutputTo).

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Emily Edgington said:
I am using Access 2000 on Windows XP. I am trying to export a query to
a
txt
file while maintaining the decimal places on particular fields. I have
3
fields in my query for which I want more than 2 decimals places.

I have seen many postings on this issue, and have tried the solutions
to
no
avail. Someone suggested adjusting the regional and language settings
in
control panel. This worked, except that all of my decimal fields
exported
with 5 decimals places. No good.

I've tried the suggestions for forcing the formatting, using Format()
and/or
CStr(). For some reason, that is not working for me. I still get the
same 2
decimal place results no matter what I do. When I export to xls, I get
all
of the decimals I specify.

Below is my query sql. Qty, UnitPrice, and ExtAmt are the 3 fields I
am
struggling with. Can anyone help?

Thanks,
Emily

SELECT tblOutput.APBU, tblOutput.VndrID, tblOutput.AddrSeq,
tblOutput.VndrNm, tblOutput.VndrAddr, tblOutput.VndrCity,
tblOutput.VndrSt,
tblOutput.VndrZip, tblOutput.BU, tblOutput.BUNm, tblOutput.InvDt,
tblOutput.InvID, tblOutput.DelDt, tblOutput.OrdDt, tblOutput.Total,
tblOutput.Tax, tblOutput.Freight, tblOutput.DelAddr, tblOutput.DelCity,
tblOutput.DelSt, tblOutput.DelZip, CStr([Qty]) AS Qty4,
tblOutput.ItemDescr,
tblOutput.UOM, Format(CStr([UnitPrice]),"0.00000") AS UnitPrice5,
CStr([ExtAmt]) AS ExtAmt3, tblOutput.Acct, tblOutput.Dept,
tblOutput.Prod,
tblOutput.Proj, tblOutput.Origin, tblOutput.PymntMess
FROM tblOutput
ORDER BY tblOutput.APBU, tblOutput.VndrID, tblOutput.AddrSeq,
tblOutput.InvDt, tblOutput.InvID;
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell MVP

Use a macro or VBA code to run TransferText action. Do not use an export
specification. If the number exports with " characters around it, then
you'll need to create an export specification that will not put " characters
around text values.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




Emily Edgington said:
Thank you for your quick response! I am using File - Export. What option
do
I have that doesn't override the formatting?

Ken Snell MVP said:
How are you exporting the query's data? If by OutputTo, it overrides any
formatting that you put in the query. If by TransferText and you use an
Export Specification, the specification will overwrite any formatting
that
you put in the query. If by File | Export, it will overwrite the
formatting
that you put in the query (essentially similar to using OutputTo).

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Emily Edgington said:
I am using Access 2000 on Windows XP. I am trying to export a query to
a
txt
file while maintaining the decimal places on particular fields. I have
3
fields in my query for which I want more than 2 decimals places.

I have seen many postings on this issue, and have tried the solutions
to
no
avail. Someone suggested adjusting the regional and language settings
in
control panel. This worked, except that all of my decimal fields
exported
with 5 decimals places. No good.

I've tried the suggestions for forcing the formatting, using Format()
and/or
CStr(). For some reason, that is not working for me. I still get the
same 2
decimal place results no matter what I do. When I export to xls, I get
all
of the decimals I specify.

Below is my query sql. Qty, UnitPrice, and ExtAmt are the 3 fields I
am
struggling with. Can anyone help?

Thanks,
Emily

SELECT tblOutput.APBU, tblOutput.VndrID, tblOutput.AddrSeq,
tblOutput.VndrNm, tblOutput.VndrAddr, tblOutput.VndrCity,
tblOutput.VndrSt,
tblOutput.VndrZip, tblOutput.BU, tblOutput.BUNm, tblOutput.InvDt,
tblOutput.InvID, tblOutput.DelDt, tblOutput.OrdDt, tblOutput.Total,
tblOutput.Tax, tblOutput.Freight, tblOutput.DelAddr, tblOutput.DelCity,
tblOutput.DelSt, tblOutput.DelZip, CStr([Qty]) AS Qty4,
tblOutput.ItemDescr,
tblOutput.UOM, Format(CStr([UnitPrice]),"0.00000") AS UnitPrice5,
CStr([ExtAmt]) AS ExtAmt3, tblOutput.Acct, tblOutput.Dept,
tblOutput.Prod,
tblOutput.Proj, tblOutput.Origin, tblOutput.PymntMess
FROM tblOutput
ORDER BY tblOutput.APBU, tblOutput.VndrID, tblOutput.AddrSeq,
tblOutput.InvDt, tblOutput.InvID;
Click to expand...
Click to expand...
Click to expand...
 
E

Emily Edgington

I tried using TransferText without an export specification, but I need a
fixed width file output, so I was not able to. I had to recreate my export
specification because I changed some dates to text and my previous one was no
longer working, and in doing so, my decimals are now exporting fine, using
Format(CStr([field]),"0.0000"). I don't like that I don't know why it works
now, but at least it does.

Thanks!

Ken Snell MVP said:
Use a macro or VBA code to run TransferText action. Do not use an export
specification. If the number exports with " characters around it, then
you'll need to create an export specification that will not put " characters
around text values.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




Emily Edgington said:
Thank you for your quick response! I am using File - Export. What option
do
I have that doesn't override the formatting?

Ken Snell MVP said:
How are you exporting the query's data? If by OutputTo, it overrides any
formatting that you put in the query. If by TransferText and you use an
Export Specification, the specification will overwrite any formatting
that
you put in the query. If by File | Export, it will overwrite the
formatting
that you put in the query (essentially similar to using OutputTo).

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


message I am using Access 2000 on Windows XP. I am trying to export a query to
a
txt
file while maintaining the decimal places on particular fields. I have
3
fields in my query for which I want more than 2 decimals places.

I have seen many postings on this issue, and have tried the solutions
to
no
avail. Someone suggested adjusting the regional and language settings
in
control panel. This worked, except that all of my decimal fields
exported
with 5 decimals places. No good.

I've tried the suggestions for forcing the formatting, using Format()
and/or
CStr(). For some reason, that is not working for me. I still get the
same 2
decimal place results no matter what I do. When I export to xls, I get
all
of the decimals I specify.

Below is my query sql. Qty, UnitPrice, and ExtAmt are the 3 fields I
am
struggling with. Can anyone help?

Thanks,
Emily

SELECT tblOutput.APBU, tblOutput.VndrID, tblOutput.AddrSeq,
tblOutput.VndrNm, tblOutput.VndrAddr, tblOutput.VndrCity,
tblOutput.VndrSt,
tblOutput.VndrZip, tblOutput.BU, tblOutput.BUNm, tblOutput.InvDt,
tblOutput.InvID, tblOutput.DelDt, tblOutput.OrdDt, tblOutput.Total,
tblOutput.Tax, tblOutput.Freight, tblOutput.DelAddr, tblOutput.DelCity,
tblOutput.DelSt, tblOutput.DelZip, CStr([Qty]) AS Qty4,
tblOutput.ItemDescr,
tblOutput.UOM, Format(CStr([UnitPrice]),"0.00000") AS UnitPrice5,
CStr([ExtAmt]) AS ExtAmt3, tblOutput.Acct, tblOutput.Dept,
tblOutput.Prod,
tblOutput.Proj, tblOutput.Origin, tblOutput.PymntMess
FROM tblOutput
ORDER BY tblOutput.APBU, tblOutput.VndrID, tblOutput.AddrSeq,
tblOutput.InvDt, tblOutput.InvID;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Export to .txt tuncates decimals 6

Top