Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Number formatting (97 SR1)

    This should be simple, but I can't figure it out.
    What should I enter as the format property so that if the number is zero, it doesn't appear, if it's a decimal, it shows up with the entered number of decimal places, and if it's integral, it doesn't show the decimal point? I've tried many variations, and none that I've tried have worked right. The Data Type is Number - Single. Thanks in advance for your help.

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

    Re: Number formatting (97 SR1)

    The critical question is where are you trying to control this format? You can do this in a form easily. You can't do it in a table, and there isn't any real reason to do it in a query. If you explain exactly what you're trying to accomplish in applying this formatting scheme, maybe someone can help you do it.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Feb 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number formatting (97 SR1)

    Thanks for the quick response. Yes, it is for a form. I haven't made the corresponding reports yet, but I assume the formatting will be similar.

  4. #4
    New Lounger
    Join Date
    Feb 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number formatting (97 SR1)

    Okay, I think I've got it. At the Query level, when summing three columns that may or may not be null, I used the NZ function. Then I used the Iif function to set the total to null if the total was zero. That got rid of the zeros and I could simply use the 'general number' format on my form/reports. So in my query I had two calculated fields as follows:

    DoseTotal: NZ([DoseB]) + NZ([DoseG]) + NZ([DoseN])
    DoseTot: Iif([DoseTotal]=0, Null, [DoseTotal])

    And I'll just use 'DoseTot' on my form and reports. I wish the arithmetic operators would see null values as zeroes, but I guess there is a good reason why they don't work that way. I'm converting our survey recording system to access from excel, where I could have just set the options to not show zero values. I guess with greater flexibility comes greater complexity. Have a great day. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number formatting (97 SR1)

    Keep in mind that you have the option of specifying custom formats for positive;negative;zero;null values in the format property (similar to Excel's custom numeric formats). Search Access help for "Format Property

Posting Permissions

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