Access losing number format when outputting to Excel

[slickdock]

I have an MSAccess report whose property is =Sum([Amount]). I formatted it as
Standard with 2 decimal places and it looks perfect in msAccess. Examples:
100.00
0.00
35.42

When I use msAccess' OutputTo Excel feature, the amount column looks like
this:
100
0
35.42

What am I doing wrong? I don't want the users to have to do additional work
when it outputs to Excel. How can I make it output from msAccess properly?

Thanks in advance.

 

[John Spencer]

It is exporting properly. Access is using a format to control the display of
the data. If you want the data displayed that way in EXCEL then Excel needs
to use a format to control the display of the data.

There is really nothing you can do except...
I don't know if this will work, but you can try changing the control source to
=Format(Sum(Amount),"#,##0.00")

It is possible that Access will then see this value as a string and export it
correctly. I don't think it will work, but you can try it and see.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[ryguy7272]

I already answered this in the Excel Programming DG.
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

It is exporting properly. Access is using a format to control the display of
the data. If you want the data displayed that way in EXCEL then Excel needs
to use a format to control the display of the data.

There is really nothing you can do except...
I don't know if this will work, but you can try changing the control source to
=Format(Sum(Amount),"#,##0.00")

It is possible that Access will then see this value as a string and export it
correctly. I don't think it will work, but you can try it and see.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have an MSAccess report whose property is =Sum([Amount]). I formatted it as
Standard with 2 decimal places and it looks perfect in msAccess. Examples:
100.00
0.00
35.42

When I use msAccess' OutputTo Excel feature, the amount column looks like
this:
100
0
35.42

What am I doing wrong? I don't want the users to have to do additional work
when it outputs to Excel. How can I make it output from msAccess properly?

Thanks in advance.

 

[slickdock]

Thank you, but you're right...it didn't work. What I was able to do, since
the answer below was too complicated for me, was simply create a query that
summed the numbers, then link that query to my final query. That allowed me
to remove the =Sum([amount]) from the report, and simply use [amount] in the
report, which I am able to format as fixed, with 2 decimals. Now it exports
the way I want it in excel.

I also don't know how to post once with a "cc" to another group, so that I
don't post the same question twice. Can you help?

Thank you.

It is exporting properly. Access is using a format to control the display of
the data. If you want the data displayed that way in EXCEL then Excel needs
to use a format to control the display of the data.

There is really nothing you can do except...
I don't know if this will work, but you can try changing the control source to
=Format(Sum(Amount),"#,##0.00")

It is possible that Access will then see this value as a string and export it
correctly. I don't think it will work, but you can try it and see.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have an MSAccess report whose property is =Sum([Amount]). I formatted it as
Standard with 2 decimal places and it looks perfect in msAccess. Examples:
100.00
0.00
35.42

When I use msAccess' OutputTo Excel feature, the amount column looks like
this:
100
0
35.42

What am I doing wrong? I don't want the users to have to do additional work
when it outputs to Excel. How can I make it output from msAccess properly?

Thanks in advance.