C
CP
I recently upgraded from AccessXP to Access 2003. Earlier, I
developed a query that I run pretty often with a number of columns
containing Currency data. Using Access XP, I formatted these columns
as Currency by using the Properties choice for the field/column so
the
query results show as Currency. I then choose the "Analyze with
Excel" choice from the Office Links menu. Excel opens and the
resulting spreadsheet shows the columns as Currency (i.e. with a "$"
character) even though the Cell Format was set to Custom = $#,##0.00;
($#,##0.00). Although this wasn't REALLY currency, it was okay
because the cells could be treated as currency and could be summed,
multiplied, etc. And they APPEARED to be Currency (because of the
"$") which is what I needed to show in reports.
However when I run the very same query in Access 2003 the resulting
spreadsheet shows the cells as numbers and the formatting is Custom =
#,##0.00;(#,##0.00), i.e.WITHOUT the "$". Everytime I run this query
and create the resulting spreadsheet, I have to change the formatting
in Excel for each affected column to Currency (and there are a bunch
of them separated by other columns here and there that aren't
currency
format so I can't format all of them at once). It isn't difficult to
do this (manually or with a macro) but I'm really curious about what
changed between the two programs to cause this change. I've looked
through Tools|Options in Excel but can't find anything that appears
to
be related to this type of behavior. And I'm using the same computer
for both AccXP and Acc2003 so the Regional settings didn't change.
(I also tried to get around this problem by using the
FormatCurrency()
and Format() functions as well as combining those functions with the
Ccur() function in Access but those cells became TEXT in the
resulting
spreadsheet which helped less than before.)
I'd appreciate it greatly if anyone has any ideas about this change
or
can explain why this is happening. And if you can tell me any
alternate ways to retain the Access formatting in an Excel
spreadsheet
without having to reformat columns or run a macro, that would be
great.
Thanks in advance. Carol.
developed a query that I run pretty often with a number of columns
containing Currency data. Using Access XP, I formatted these columns
as Currency by using the Properties choice for the field/column so
the
query results show as Currency. I then choose the "Analyze with
Excel" choice from the Office Links menu. Excel opens and the
resulting spreadsheet shows the columns as Currency (i.e. with a "$"
character) even though the Cell Format was set to Custom = $#,##0.00;
($#,##0.00). Although this wasn't REALLY currency, it was okay
because the cells could be treated as currency and could be summed,
multiplied, etc. And they APPEARED to be Currency (because of the
"$") which is what I needed to show in reports.
However when I run the very same query in Access 2003 the resulting
spreadsheet shows the cells as numbers and the formatting is Custom =
#,##0.00;(#,##0.00), i.e.WITHOUT the "$". Everytime I run this query
and create the resulting spreadsheet, I have to change the formatting
in Excel for each affected column to Currency (and there are a bunch
of them separated by other columns here and there that aren't
currency
format so I can't format all of them at once). It isn't difficult to
do this (manually or with a macro) but I'm really curious about what
changed between the two programs to cause this change. I've looked
through Tools|Options in Excel but can't find anything that appears
to
be related to this type of behavior. And I'm using the same computer
for both AccXP and Acc2003 so the Regional settings didn't change.
(I also tried to get around this problem by using the
FormatCurrency()
and Format() functions as well as combining those functions with the
Ccur() function in Access but those cells became TEXT in the
resulting
spreadsheet which helped less than before.)
I'd appreciate it greatly if anyone has any ideas about this change
or
can explain why this is happening. And if you can tell me any
alternate ways to retain the Access formatting in an Excel
spreadsheet
without having to reformat columns or run a macro, that would be
great.
Thanks in advance. Carol.