Formating Excel Cells

[AlienzDDS]

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.

 

[tina]

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

 

[AlienzDDS]

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
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:

"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.