Format number as 0.0k

[Nathan Bloomfield]

Is it possible to setup a number format in a query to display result
as

x.xK (4.0K instead of 4000)

I am trying to abbreviate the numbers contained in graphs which are
not displaying correctly due to too many values on the x axis. I
would prefer to not to use an "iif" formula but utilise the "format"
function.

The following format works for excel, but is not compatible with
access:
[<1000000]$###.0,K;$###.0,,\M

Hope someone can help.

[Salad]

Nathan Bloomfield wrote:

Is it possible to setup a number format in a query to display result
as

x.xK (4.0K instead of 4000)

I am trying to abbreviate the numbers contained in graphs which are
not displaying correctly due to too many values on the x axis. I
would prefer to not to use an "iif" formula but utilise the "format"
function.

The following format works for excel, but is not compatible with
access:
[<1000000]$###.0,K;$###.0,,\M

Hope someone can help.

lngNum = 10000000.5
? format(lngNum,"00.0K")
10000000.5K

[Steve Jorgensen]

On Mon, 20 Dec 2004 06:56:58 GMT, Salad <oi*@vinegar.com> wrote:

Nathan Bloomfield wrote:

Is it possible to setup a number format in a query to display result
as

x.xK (4.0K instead of 4000)

I am trying to abbreviate the numbers contained in graphs which are
not displaying correctly due to too many values on the x axis. I
would prefer to not to use an "iif" formula but utilise the "format"
function.

The following format works for excel, but is not compatible with
access:
[<1000000]$###.0,K;$###.0,,\M

Hope someone can help.

lngNum = 10000000.5
? format(lngNum,"00.0K")
10000000.5K

I believe he wanted it to format a number in terms of its value in K=1000, so
for 10000000.5, he wants the format to be 10000.0005K

[nathanbloomfield]

That is correct, Steve.

I would like to abbreviate the numbers in the format "0.0k"

e.g. 5000 would be 5k, 10,500 would be 10.5k
I have yet to find a solution. Any help will be much appreciated.

[Darryl Kerkeslager]

<na**************@hotmail.com> wrote

That is correct, Steve.

I would like to abbreviate the numbers in the format "0.0k"

e.g. 5000 would be 5k, 10,500 would be 10.5k
I have yet to find a solution. Any help will be much appreciated.

Could you use two fields, one for the actual value:
n=5500

the other just for the label

Format(n/1000,"00.0k")

?

Darryl Kerkeslager

[Trevor Best]

na**************@hotmail.com wrote:

That is correct, Steve.

I would like to abbreviate the numbers in the format "0.0k"

e.g. 5000 would be 5k, 10,500 would be 10.5k
I have yet to find a solution. Any help will be much appreciated.

Function KFormat(pvarNumber As Variant) As Variant
Dim curNumber As Currency

If IsNull(pvarNumber) Then
' GIGO factor :-)
KFormat = Null
Else
If IsNumeric(pvarNumber) Then
curNumber = pvarNumber
If Int(curNumber) = curNumber Then
' Integer
KFormat = Format(curNumber, "#,##0\k")
Else
KFormat = Format(curNumber, "#,##0.0###\k")
End If
Else
' More GIGO factor.
' what do you want to do here?
' Return a null?
' Return an error message?
' Raise an Error?
' Emulate Access?
KFormat = "#num?"
End If
End If
End Function
--
This sig left intentionally blank