Formating Excel Cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AlienzDDS - 04 Aug 2005 18:48 GMT

I have read through all the other posts on this issue and found this:

"In another post, I noticed Ken Snell recommending the following to preserve
formatting when exporting a time column to Excel:

MyTimeField: Format([TimeFieldName], "hh:nn")"

Where would this code go?

I am using transferspreadsheet to export a query to excel but I need to
specify a couple of format issues. Even when the query is in the correct
format it does not convert correctly to excel.

This first column is a date and needs to be mmm-yy and all other columns
must not display zeros.

Thanks in advance.

Reply to this Message

tina - 05 Aug 2005 01:15 GMT

it goes in the query, as a calculated field. "MyTimeField:" defines the
alias ("fake" name) for the calculated field. if you don't define an alias
yourself, Access will assign one (Expr1, Expr2, etc).

if you set the calculated field, and then look at the query's datasheet
view, you will see the column named MyTimeField - with the data from your
time field displayed in the "hh:nn" format.

if it's not displaying that way in the query's datasheet (*before* you
export it), then post the query's SQL so we can look at it.

hth

> I have read through all the other posts on this issue and found this:
>
[quoted text clipped - 13 lines]
>
> Thanks in advance.

Reply to this Message

AlienzDDS - 19 Aug 2005 12:36 GMT

Thanks Tina,

This sort of worked. The problem is the format for the date that we need is
"mmm-yy" and using this method the dates are not recognized by excel as
dates. So April and August come to the top.

As a temporary fix I am having the user save over the same spreadsheet which
is already formatted. This is not ideal and also after being used several
times we get an error of too many fields. We can delete the spreadsheet and
make a new one and the error doesn't come back.

Thank you for your help.

> it goes in the query, as a calculated field. "MyTimeField:" defines the
> alias ("fake" name) for the calculated field. if you don't define an alias
[quoted text clipped - 27 lines]
> >
> > Thanks in advance.

Reply to this Message