Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Decimals in Merge with Word (Office 97)

    I imported an excel spreadsheet into an Access table. Then I ran a query and am using this query to do a mail merge with Word. The problem I am having is with the percentage fields. When I do the merge, they appear as decimals, not percenages, and they don't stop at the 5th decimal place as I indicated in the table. The percentage fields in the table are as follows:
    Field Size: Double
    Format: Percent
    Decimal Places: 5

    With these settings, the fields appear exactly as I want them to in Access but when I merge with word I get decimals and not percentages and the decimal never ends. Any ideas? Thanks.

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Decimals in Merge with Word (Office 97)

    Recommend use Format function in a query to format numbers before running mail merge. For more detailed info take a look at these MSKB articles:

    ACC2000: How to Use a Query to Format Data for a Mail Merge

    ACC: How to Use a Query to Format Data for a Mail Merge

    The first article refers to Access 2000 but the info is equally applicable to Access 97.

    HTH

  3. #3
    Star Lounger
    Join Date
    Jan 2002
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Decimals in Merge with Word (Office 97)

    This is my query when I look at it in SQL View:

    SELECT [00QMCSpec].Provider, [00QMCSpec].Field3 AS [Last Name], [98QMCSpec].Members, [98QMCSpec].Paid, [98QMCSpec].[% Members], [98QMCSpec].[% Claims $$], [98QMCSpec].[% of Total], [98QMCSpec].Distribution, [99QMCSpec].Members, [99QMCSpec].Paid, [99QMCSpec].[% Members], [99QMCSpec].[% Claims $$], [99QMCSpec].[% of Total], [99QMCSpec].Distribution, [00QMCSpec].Members, [00QMCSpec].Paid, [00QMCSpec].[% Members], [00QMCSpec].[% Claims $$], [00QMCSpec].[% of Total], [00QMCSpec].Distribution, [00QMCSpec]![Distribution]+[98QMCSpec]![Distribution]+[99QMCSpec]![Distribution] AS Total
    FROM (00QMCSpec INNER JOIN 98QMCSpec ON [00QMCSpec].[Provider ID] = [98QMCSpec].[Provider ID]) INNER JOIN 99QMCSpec ON ([00QMCSpec].[Provider ID] = [99QMCSpec].[Provider ID]) AND ([98QMCSpec].[Provider ID] = [99QMCSpec].[Provider ID]);

    I tried to add the following to the end of the query, like the article stated:

    Field: 98%Members: Format ( [98QMCSpec].[% Members], "0%" )

    And it didn't work. I got the message:
    Characters found after end of SQL statement.

    Sorry, I'm pretty new at this so I'm not sure what I did wrong. I need all the fields in the query that have a % sign in them to be in that format for the mail merge. Any help is greatly appreciated.

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Decimals in Merge with Word (Office 97)

    Each field that you want to format has to be formatted individually using Format function. You just can't add a statement at end of query. Try modifying SQL to something like this:

    SELECT [00QMCSpec].Provider, [00QMCSpec].Field3 AS [Last Name], [98QMCSpec].Members, [98QMCSpec].Paid, Format([98QMCSpec].[% Members],"0%") AS [Members 98 %], Format([98QMCSpec].[% Claims $$],"0%") AS [Claims 98 %], Format([98QMCSpec].[% of Total],"0%") AS [Total 98 %] , [98QMCSpec].Distribution, [99QMCSpec].Members, [99QMCSpec].Paid, Format([99QMCSpec].[% Members],"0%") AS [Members 99 %], Format([99QMCSpec].[% Claims $$],"0%") AS [Claims 99%], Format([99QMCSpec].[% of Total],"0%") AS [Total 99 %], [99QMCSpec].Distribution, [00QMCSpec].Members, [00QMCSpec].Paid, Format([00QMCSpec].[% Members],"0%") AS [Members 00 %], Format([00QMCSpec].[% Claims $$],"0%") AS [Claims 00 %], Format([00QMCSpec].[% of Total],"0%") AS [Total 00 %], [00QMCSpec].Distribution, [00QMCSpec].[Distribution]+[98QMCSpec].[Distribution]+[99QMCSpec].[Distribution] AS Total
    FROM (00QMCSpec INNER JOIN 98QMCSpec ON [00QMCSpec].[Provider ID] = [98QMCSpec].[Provider ID]) INNER JOIN 99QMCSpec ON ([00QMCSpec].[Provider ID] = [99QMCSpec].[Provider ID]) AND ([98QMCSpec].[Provider ID] = [99QMCSpec].[Provider ID]);

    I have no way of testing this since I don't have any of the tables used by query, but this should give you an idea of how to do this. Copy & paste SQL statement into blank query SQL window & see if it works. Change the aliases (the expressions in brackets following the AS keyword) as desired. By the way, I don't think it's a good idea to name fields in your tables with punctuation symbols like "%", I'd recommend avoiding spaces and punctuation symbols in field names. You can always provide an alias for the field when designing query as shown above.

    HTH

  5. #5
    Star Lounger
    Join Date
    Jan 2002
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Decimals in Merge with Word (Office 97)

    Thank you so much! It worked. Now I have to format the currency fields. Is it like this: Format([98QMCSpec].Paid, "$")?

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Decimals in Merge with Word (Office 97)

    It depends what format you want to be displayed. You might try:

    Format([CurrencyField],"$#.00")

    and see if this produces desired results (This format will display at least one number for dollars, two decimal places for cents, unless amount is less than a dollar, will display "$.99"). You can experiment with Format function (see Help files for details on options you can use to format numbers & currency.) Replace [CurrencyField] with your actual field names.

    HTH

  7. #7
    Star Lounger
    Join Date
    Jan 2002
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Decimals in Merge with Word (Office 97)

    Thanks Again Mark. I figured out the correct format and thank you for all the tips!

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Decimals in Merge with Word (Office 97)

    Note that you can also use the format capabilities of Word to format a merge field. It's not as powerful as the Access query approach, but it will let you do things with Dates and Currency fields.
    Wendell

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Decimals in Merge with Word (Office 97)

    Wendell,

    I have found that it's better to do the formatting in Access in an international environment. Numeric values are merged to Word in US format (point as decimal separator); on a system with comma as decimal separator, trying to apply a number format with * leads to incorrect results. When you format the numbers in the query, Access takes the local settings into account.

    Note : this behavior may be due to the fact that I use ODBC to link to the Access data, not DDE; I haven't tested this.

  10. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Decimals in Merge with Word (Office 97)

    Word fields can be a powerful tool, but I've always found them a bit arcane. As noted in Woody's book Special Edition Using Microsoft Office 2000:
    <hr>Word includes a wasp's nest of fields, many of which have become outdated over the years and remain available only so documents created in older versions of Word will still work in Word 2000. These fields and their switches and settings comprise an entire programming language unto itself, buried inside Word, and completely separate from the language used in macros: Visual Basic for Applications.
    ... Many Word field codes are poorly documented, and you can expect to lose precious time trying to make them work properly. ... And because error messages are few and far between, debugging field codes is usually a tedious, repeated trial-and-error process.<hr>
    For this reason I find it simpler to do any formatting or other manipulation in Access, using plain ole VBA, before exporting data to Word via Mail Merge or Automation, than trying to decipher Word's somewhat cryptic & arcane field syntax.

Posting Permissions

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