Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Zero Values (Access 2000)

    Hi,
    How can I suppress the displaying of zero values (currency) in tables and forms?

    Thank you
    Louise

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

    Re: Zero Values (Access 2000)

    Use the format function :
    $#.##,$-#.##<!t>[Red]<!/t>,""
    The first part is for positive numbers ($#.##)
    The second part is for negative numbers ($-#.##<!t>[Red]<!/t>)
    The third part is for zero values ("")
    Francois

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zero Values (Access 2000)

    Francois,

    Thank you very much!
    Louise

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zero Values (Access 2000)

    Francois,
    I changed the "Currency" value (in the properties window under format) to the formula you suggested and now everything displays in red with <font color=red>$.,$-., <font color=black> in both the table and the form. I obviously entered it in the wrong place? More help please?

    Thanks
    Louise

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

    Re: Zero Values (Access 2000)

    You have to enter <!t>[Red]<!/t> in the second part. Think in three parts
    $#.## , $-#.##<!t>[Red]<!/t> , ""
    pos neg Zero
    If you don't want the negative numbers to be red, just remove <!t>[Red]<!/t>
    Francois

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zero Values (Access 2000)

    Francois,
    I understand the formula. (I also took a shortcut and copied and pasted it from your post. <img src=/S/smile.gif border=0 alt=smile width=15 height=15> ) I use something similar quite often in Excel. Makes perfect sense but am I entering the formula in the right place in Access? In the properties window under format? Its not one of the choices in the drop down menu.

    Thanks again
    Louise

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

    Re: Zero Values (Access 2000)

    Yes, this is the right place. The drop down menu provide a few standard formats but you're not limited to these. You can use any custom format you want.
    Francois

  8. #8
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zero Values (Access 2000)

    Hi Francois,
    Thanks for your help! I got it to work by changing your formula to $#,##;$-#,##{Red};"". <font color=black>I changed the comma to semi-colons between each section and the period to commas for currency.
    Thanks for getting me on the right track!

    By the way, I know that the "Red" is in brackets but I don't know how to have that show up here like you did without the sentence just turning red.
    Louise

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

    Re: Zero Values (Access 2000)

    Sorry about the punctuation. Formatting numbers and dates get sometimes <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15> with all the different international settings.

    To get <!t>[Red]<!/t> in a post in the lounge, you have to use :
    <!t>[t]<!/t>Red<!t>[/t]<!/t>
    Francois

  10. #10
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zero Values (Access 2000)

    No problem!
    Thanks for the tip on the "red" <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Louise

Posting Permissions

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