Format Problems Exporting to Excel (Access 97 SR-2)

Topic

New Reply

Hi Everyone,
I am a VERY new user of Access so bear with me. I am trying to export data to Excel 97 with the money fields formatted as 1,200 or (1,200). In other words, as a number with zero decimal places and using a comma. No matter what I do I can’t get the data in Excel the way I need it. BTW, when I get it to Excel there will 10-15 separate tabs of data (resulting from multiple queries) which is why I don’t want to have to format it in Excel if I don’t have to. So, can anyone tell me what I’m missing?

This is what I have tried so far:
– Making the data type a number with standard format and zero decimals. The result in Excel is 1200 or -1200.
– Changing the data type to currency with a currency format and zero decimals exports to Excel as $1,200.00.
– Making the data type currency with a standard format and zero decimal exports as $1,200.00

TIA

Reply | Quote

Replies

If you apply a format to a number field in Access, this only changes the way the field is displayed in Access, but not the underlying data. When you export the data to Excel, you export the numbers, not the format (except, if the data type is currency, the export routine knows that it should be formatted as currency in Excel).

You can create a query that returns the numeric values as formatted text. When you export the query to Excel, the displayed values will be exactly as in the Access query. But … they will be text, not numbers, so you can’t use them in calculations. (There are ways to convert these text values to numbers again in Excel, but that defeats your purpose, I think.)

This is how you create a formatted text field in a query: type the name you want to give the field, then a colon, then Format([Fieldname], “#.##0”), where Fieldname must be replaced by the actual name of the field. So, if you want to export a field named Price as FormattedPrice, use

FormattedPrice: Format([Price], “#.##0”)

You write that you are new to Access. I don’t know how familiar you are with Excel. If all the “tabs” have the same layout, you can apply the desired format to all of them by selecting multiple worksheets. Otherwise, you might create a macro that applies the desired format. In both cases, you will have numbers in Excel that you can use for further calculations.

Reply | Quote

Hans,
Thanks for the info. I suspected that the formatting was for Access only. I am a little confused about your instructions for creating a formatted text field in a Query. First of all I’m thinking I would type the – FormattedPrice: Format([Price],

Reply | Quote

I too fear that doing the formatting in Excel is the way to go.

But for your information, you type calculated fields in the Field row of the query grid. I have attached a screen shot to illustrate this. The captions are in Dutch, but I hope you’ll get the idea.

Reply | Quote