Full Version: Delete Trailing Zeros
UtterAccess Forums > Microsoft® Access > Access Forms
expatriate
Hi,
I want to format textboxes so that the following entries display 4 decimal places but no trailing zeros, like:
displays 4
.5 displays .5
.51227733 displays .5123 (or .5122 is also OK, but just 4 places.)
.5122 displays .5122
1.512222 displays 1.5122
can it be done?
Thank you.
datAdrenaline
If you only want four decimal places, and your data is in a Table, then change the datatype to Currency (NOT the format property -- but the actual datatype!), then set the Format property of the field to General Number if the values do not represent a monetary amount.
If you don't want to go that route, or are unable to, then to change the value a numeric value is displayed, then modify the Format property of the text box control used to display that value with a setting that looks like this:
0.####
But note that only the display is altered, the actual value is still extended beyond 4 decimal places.
Other options are to use the Round() function in the Control Source of the text box ...
=Round([someFieldOrControlName],4) -- Note that bankers rounding is used.
FOr the Format() function ...
=Format([someFieldOrControlName], "0.####") -- Note that Format() returns a string value, so you may need to coerce the displayed value back to a number when using in a mathematical expression.
expatriate
the problem with this is 5 displays 5., not 5 The currency approach is intriguing, though. I will try that.
Thank you.
Gustav
This may be useful:
Val(Str(Format(YourValue,"0.####")))
though it will display .4 as 0.4.
Else try this:
=IIf(Abs(YourValue)<1,Format(YourValue, "#.####"),CStr(Val(str(Format(YourValue, "#.####")))))
/gustav
John Spencer
The following should work and handle values that are positive, negative, zero, or null
ormat([TheNumber],IIF(Int([TheNumber])=[TheNumber],"#,##0","#,###.####")
expatriate
That looks like it'll work. Very slick! Thanks.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.