Hello,
I have problem of formating numbers on the MS Access report.
For example, I would like to format:
1) 25,000,000 result ---> 25M
2) 8,200,000 result ---> 8.2M
3) 234,000 result ----> 234K

would someone help me out on these? Is it possible to format &
reflect these different results on one field?
Thanks so much in advance!!

Access doesn't offer that sort of formatting.

You'll have to write a custom function and use it. Something like:

Function MyFormat(InputValue As Long) As String

If InputValue > 1000000 Then
MyFormat = InputValue / 1000000 & "M"
ElseIf InputValue > 1000 Then
MyFormat = InputValue / 1000 & "K"
Else
MyFormat = InputValue
End If

End Function


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

"Orchid" <yhtong86 (AT) yahoo (DOT) com> wrote

Small expansion on Douglas Steele's advice.

You might want to apply rounding to the calculation result before adding the
suffix. Otherwise you might get something like the following

25,565,721 ---> 25.565721M

Assuming that you want up to two places after the decimal.
MyFormat = Round(InputValue / 1000000,2) & "M"

If you always want 2 decimal places you might prefer using the Format function.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Douglas J. Steele wrote: