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

    Zero Values in Access (Office 2000)

    Hello,
    I have a calculated field in a query that simply adds the value of other fields together. I use the Nz function if the field is null for it to work properly.

    The results for most is zero which is correct since the field calculates differences, but what I would like is for it to NOT show the zero like in Excel, where I can un-check the "show Zero values". This would make it easier for me to see since the differences would stand out instead of scrolling thru a bunch of zeroes looking for 10.00 or something.

    Can this be done in Access as well?

    Thanks
    Louise

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

    Re: Zero Values in Access (Office 2000)

    You can set the Format property of the field and/or of a text box bound to the field to <code>0%;;""</code>. The empty string in the third section of the format hides zero values.

  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 in Access (Office 2000)

    Hi Hans!

    Thank you very much! You're fantastic! The only change I made was from 0% to 0.00 so that the result is in amounts instead of percentage.

    Thanks again!
    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 in Access (Office 2000)

    Hans,
    It worked so well that I decided to apply the format to other fields as well. It doesn't work for some reason on the fields that have the following formula: Fri: Nz(IIf([EntryFee]>1,(Nz([ADD'L Anglers]/250)*2)+2,0)) Do I have the "Nz"s in the wrong place? I tried changing things around but it didn't like what I was trying to do.

    Thanks again,
    Louise

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

    Re: Zero Values in Access (Office 2000)

    I don't think you need the outer Nz here, since the IIf function never returns Null. Does this work better?

    Fri: IIf([EntryFee]>1,(Nz([ADD'L Anglers]/250)*2)+2,0)

  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 in Access (Office 2000)

    Hans,
    Yes, that works much better. Thank you very much.
    For some reason I thought that Nz had to be "attached" or next to the field names that could possibly have a null value so I included one for every field in the formula. In this case, the "EntryFee" field could be empty so I put one there too. <img src=/S/doh.gif border=0 alt=doh width=15 height=15> Learn something new every day! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Thanks again!
    Louise

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

    Re: Zero Values in Access (Office 2000)

    You could have used IIf(Nz([EntryFee])>1, ...) but it's not really necessary. If EntryFee is Null, the condition [EntryFee]>1 is not True anyway

Posting Permissions

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