DoCmd.OutputTo date format

[choaspes]

Dear All

When I use DoCmd.Outputto to create a spread sheet from a query, most (but not all) of the date fields appear in the resultant spread sheet in the format DD-Mon-YY, despite the fact that hh:mm is included in the data in each cell (which you can see when you click on it).

Is there any way I can persuade the spread sheet to automatically display all the dates in the format "dd/mm/yyyy hh:mm" without using the Format function in the query and converting all the dates to strings?

It's no trouble for me to open up the XLS and reformat the columns manually, but some people who will use this functionality are fairly computer illiterate and won't know how to do that - they'll just open the spread sheet and assume it only contains date information and not times.

 

[CJ_London]

dates are numbers - showing it as dd/mm/yyyy whatever is a format property - properties are not exported/imported.

So you will need to use the format function to convert the date numeric value to a string value.

Alternative is to have an excel template already formatted and export to that.

Or use Excel to import the data and ensure the 'preserve cell formatting' is ticked

 

[choaspes]

Thanks CJ!