1. 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. 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.

3. 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. 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. 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
•