Prev: Access problem
Next: EMail RTF memo field
From: Orchid on 29 Jul 2010 17:17
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!!
From: Douglas J. Steele on 30 Jul 2010 07:02
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(a)yahoo.com> wrote in message
news:ed17b789-fda4-4af4-a15a-97fc1daa4bbc(a)f6g2000yqa.googlegroups.com...
> 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!!


From: John Spencer on 30 Jul 2010 09:05
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:
> 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
>
>
 | 
Pages: 1
Prev: Access problem
Next: EMail RTF memo field