Dates as column names export to excel as text

[manninp5]

I'm using a macro to export query results to excel. Everything works
great, except that the column names are dates that export to excel as
text. Excel formulas do not recognize it as a date, and dependent
formulas result in errors. Excel does not recognize them as a date
until I enter the cell by double clicking on it (going to format>cells
doesn't help). After exiting, the date appears in date format and can
be recognized by excel formulas. I've seen this many times before and
have never found a work around other than entering and exiting the
individual cells.

 

[Stefan Hoffmann]

hi,

I'm using a macro to export query results to excel. Everything works
great, except that the column names are dates that export to excel as
text.

Sounds like exporting a pivot query...

Excel formulas do not recognize it as a date, and dependent formulas result in errors.

I never tried it, but using the US date format may help.

In your pivot query:

Format([yourField], "#mm/dd/yyyy#") as column expression.


mfG
--> stefan <--

 

[manninp5]

Thanks for the response. It is a crosstab query running in access
2003. When I put the formula in, it runs, but returns 4008mm/dd/yyyy3
for 9/27/09, bizarre. Also, access won't let me change the group by
option to expression. Any ideas?

Thanks again,
Pat

hi,

I'm using a macro to export query results to excel.  Everything works
great, except that the column names are dates that export to excel as
text.  

Sounds like exporting a pivot query...

Excel formulas do not recognize it as a date, and dependent formulas result in errors.  

I never tried it, but using the US date format may help.

In your pivot query:

Format([yourField], "#mm/dd/yyyy#") as column expression.

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

Thanks for the response. It is a crosstab query running in access
2003. When I put the formula in, it runs, but returns 4008mm/dd/yyyy3
for 9/27/09, bizarre. Also, access won't let me change the group by
option to expression. Any ideas?

Create a query and place the Format() in it. Use this query as pivot
source...

mfG
--> stefan <--

 

[AG]

Just a wild guess, but when exporting to Excel, there is an option for
'first row included field names'.
If that is set to true, try false.
Also make sure that the field containing the data is actually a date field
and not a text field that just contains text formatted as dates.

 

[manninp5]

Interesting, the query works now. I think the problem must either be
with the way access is exporting, or maybe with excel. Even if I run
the query and manually copy and paste the results to excel, I have the
same problem. I also tried tools>office links>analyze with excel and
got the same problem.

Cheers,
Pat