Solved How to hide decimal zeros and cells with zero value and show whole numbers.

[Ajabu]

I’m trying to make an Access report look nice. I want to:

• hide zeros
• show whole numbers as whole numbers only (3 not 3.000), and
• show numbers with decimals but without any trailing zeros (3.5 not 3.500).

I will list what I’ve tried as these options may help others in different situations.

(1) Conditional formatting works in Excel but I haven’t been able to get it to work in Access. I can change all zeros to take the background colour thereby hiding them, but as number formatting and the Mod function don’t seem to exist in Access I can’t use “=MOD([Field],1)=0” (plus number format to show zero decimal places) and “=MOD([Field],1)<>0” (plus number format to show decimal places).

(2) Setting an object's format properties to “#.000;-#.000;"";""” works but the whole numbers end up with decimal places and you end up with trailing zeros in the decimals.

(3) Using a formula within an object's data property (eg “=IIf(Int([Field])=[ Field],Format([Field],"#"),Format([Field],"#.000"))” leaves the decimals with trailing zeros.

Which leaves me wondering if a VBA function is the best option. But I can’t get this to work. Here’s what I have (doesn’t work and would also leave decimal numbers with trailing zeros).

Public Function WholeNumDec(DataNum As Variant) As Variant

Dim str1 As String
Dim str2 As String

str2 = "#;-#;"""";"""""
str1 = "#.000;-#.000;"""";"""""

If Int(DataNum) = DataNum Then
DataNum = Format(DataNum, str1)
Else
DataNum = Format(DataNum, str2)
End If

End Function

(Incidentally, I have to chose a variant data type instead of double as it otherwise gives me a type mismatch error.)

Am I on the right track? Is this even possible? Any suggestions would be appreciated.

 

[arnelgp]

Don't add any Format the the textbox.
just add conditional format if it is 0.

 

[Ajabu]

Yes, that works. But if you don't limit the decimal places and end up with a big number, you get #### in the text box. So I need to also limit the decimal places.

In order to see the number I have to select Format: Standard Number plus decimal place (2). But that then gives me trailing zeros (eg 3.50) and whole numbers with two decimal places (eg 4.00).

Selecting format: General Number works ... until you get a number with many decimal places giving you #### in the textbox. Sadly it is not possible to limit decimal places if General Number formatting is chosen.

I can't find any solution to fulfil all three of my number formatting options at once while limiting the number of decimal places. Surely that's got to be a way of doing this!

 

[arnelgp]

you can add code in the Format event of your report.
but this will only be effective in Print preview since Format event
does not fire on Report view.

 

[arnelgp]

see sampleReport in Print Preview.
then see the code in the Format event of the Detail Section.
now if you want to go this path, you need to make the "Actual value" to be Invisible.

 

[Ajabu]

I was hoping there was a more general solution that would work in all views. But you've given me another option which I hadn't thought about and that works. Thank you. Thanks also for the sample file. Appreciate you making the effort on that too. So I'll mark this as solved.

Thank you for your suggested solution.