Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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? <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    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
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

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

    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.

  4. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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],
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  5. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

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

    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.
    Attached Images Attached Images
    • File Type: gif x.gif (3.4 KB, 0 views)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •