Export to Excel from Access fields format issue

Question

Hello,

I have a query in Access and need to export the data to Excel worksheet. I was able to export to Excel just fine, but the result format in some fields are messed up, like date field would return a 5 digital numbers, whereas a number would return like data field. Also, issue with decimal places in number fields.

In Access query, I formatted the field with specifying fixed decimal places like Format(expression, "0.00") and date as Format(expression, "short date").

How do I changed the Access query so instead of formatting the field, actually changed the field to specified decimal or whole number or date field?

Thanks for all the help.

Answer 1

Bill,

The data will be exported to Excel each time users run the macro, which means users have to format the fields in Excel each time? Is there a way around this? Thanks

Answer 2

I normally create a custom export function in which I can automatically format columns, fix the layout, apply formatting...

I normally keep my export query as basic as possible, and do all the formatting in my code.

Here is a sample that you can use to get you going: http://www.devhut.net/2012/04/19/ms-access-vba-export-records-to-excel/

Answer 3

When you use Format() on a date the data type gets changed to a string so "5/5/2015" ends up looking to Excel like 5 divided by 5 divided by 2015. Remove the format function and do the formatting in the excel sheet itself. Same goes for the numeric precision.