Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting (2000)

    Two formatting questions:
    1 I have a query in Access linked to a Word document for mailmerge but my currency formatting doesn't carry across- numbers end up as just numbers, not $ & cents. How can I carry the formatting over. (Note the original source of data is another database accessed using ODBC so I can't change the format in the original.)
    2 Is there a way to get zeroes to not show at all - I am used to the wonderfully flexible display options in Excel but cant see how to replicate in Access. By way of explanation, I have iff statements calculating the amounts, but if I have the false part of the statement equate to "" , I get blanks but the non blanks are formatted as text not numbers. If I try to use val to get values for the True part of the statement, I can't sort as I am now mixing text and numbers.
    Appreciate any help and much thanks for past help
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Formatting (2000)

    1: Instead of putting the field in the query, use an expression that uses the format() function.

    2: Use the IIF function in Access.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formatting (2000)

    1. You can use the Format function in your query to format your Currency values into strings. Those should carry across in the mailmerge. Alternatively, you can format the field in the Word merge document instead using Word's field properties.

    2. Your second question mixes apples and oranges. Unless you pass formatted strings to the mailmerge, the formatting is done in Word, not Access. You'll have to explain more about why you're trying to do by suppressing zeros. You'll also need to post the formula you're using rather than just describing it. Again, this may be something easier to handle in Word.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting (2000)

    Thanks for the quick response but the lights aren't on here.
    1 I am using the Properties section of Access in the query for this field to format as $ and getting a properly formatted result in the Access query. Is this what you are talking about or do I need to use the (just discovered !!) format$ function.
    2 This is a small club whose memberships are due. If they are a member of the Darts club, the cost is $1, Rugby League = $3 etc. The mailmerge sends each member a letter showing what sub clubs they are members of and the current cost of that membership, so if they decide to stay in the same sub clubs, they can see the cost of each sub club and just pay the total to rejoin with the same membership. It just looks a little unprofessional having 0 as the cost of a sub club they dont belong to . I would like to have Access/Word show this as a blank so it looks better. The formulae are very simple eg iif(DartsClubMember="Y",1,0). I tried iif(DartsClubMember="Y",1,"") and got blanks instead of zeroes as I wanted but the true portion results in a text not numeric answer, I assume because I was using "" as my FALSE option. Changing this to zero fixed that problem but created my formatting problem. Bottom line - how do I get an if statement to give me a blank if it is FALSE and a number if true
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting (2000)

    1. Yes use Format Function.
    2. Use iif(DartsClubMember="Y",1,Null)
    Francois

Posting Permissions

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