Formatting Excel date column via Access application

[Beth]

I am writing out a recordset to Excel using CopyFromRecordset. Some
of the fields in the recordset are defined in Access as dates. When
Excel opens, these fields are displayed as Julian dates, I think.
(37824 = 7/22/03)

The end use for the spreadsheet is to create pivot charts. My user
could reformat these Julian date columns to mm-yyyy in the
spreadsheet, or whatever format he would like, but he does not want to
have to do this. My question is how can I format, in my Access
application code, the date columns to automatically display mm-yyyy
format in the spreadsheet?

I tried FORMAT(mydate, "mm-yyyy") in the query in my code, but then
Excel sees this as text, and the pivot charts start ordering things as
such (so 01-2001 is followed by 01-2002, as opposed to 02-2001; Excel
is doing what it should be doing for a TEXT field, just not what I
want it to be doing).

TIA

 

[Tim Ferguson]

(e-mail address removed) (Beth) wrote in

I tried FORMAT(mydate, "mm-yyyy") in the query in my code, but then
Excel sees this as text, and the pivot charts start ordering things as
such (so 01-2001 is followed by 01-2002, as opposed to 02-2001; Excel
is doing what it should be doing for a TEXT field, just not what I
want it to be doing).

I think this is an Excel natter rather than an Access one.

"02-2004" _is_ a text string, and it's not a valid date value in any
language or country. You sometimes get into problems with "02/04" being
2nd April This Year rather than February 2004, but I don't think that is
what is getting you at the moment.

You might take a moment to consider that FORMAT(MyDate, "yyyy-mm") would
sort properly.

You might want to pass an actual date as FORMAT(MyDate, "\0\1\-mm\-yyyy")
with a dummy 01 at the front, so that Excel will sort it in the right
order.

I _think_ (although I am not sure) that you can write a custom sorting
function in Excel VBA so that you can make the proper comparison between
"12-2004" and "01-2005". But there is no computer language on earth that
will do that intrinsically.

Hope that helps


Tim F