variable decimal places

[Guest]

Hi,

Is there a way that I can use the format in the properties of a textbox to
give me variable decimal places dependant on the number?

I want decimals included up and including the third decimal. If there are no
decimals, I don't want any .000 shown

i.e. data in table as follows:
152.33948
12
152.23
6.011

I want data in report to show:
152.339
12
152.23
6.011

I tried all types of #,### and combinations #,###0 etc and got nothing to
work. I also tried all combinations of using the decimal places property with
different formats.

Anyone know how I can do it?

Basil

 

[Marshall Barton]

Is there a way that I can use the format in the properties of a textbox to
give me variable decimal places dependant on the number?

I want decimals included up and including the third decimal. If there are no
decimals, I don't want any .000 shown

i.e. data in table as follows:
152.33948
12
152.23
6.011

I want data in report to show:
152.339
12
152.23
6.011

I tried all types of #,### and combinations #,###0 etc and got nothing to
work. I also tried all combinations of using the decimal places property with
different formats.

You can use the format
#,##0.###

to get the "optional" decimal places, but you will still get
the decimal point for integer values.

If getting rid of the decimal point for whole numbers is
really important, then you have to format the numbers in
code. Make the text box bound to the number field invisible
(let's call this txtNumber). Then add another text box
named txtFormatted. The code in the Format event procedure
could the be like this:

If Me.txtNumber = Int(Me.txtNumber) Then
Me.txtFormatted = Format(Me.txtNumber, "#,##0")
Else
Me.txtFormatted = Format(Me.txtNumber, "#,##0.###")
End If