By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,089 Members | 2,301 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,089 IT Pros & Developers. It's quick & easy.

Format number as 0.0k

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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
Nov 13 '05 #2

P: n/a
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
Nov 13 '05 #3

P: n/a
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.

Nov 13 '05 #4

P: n/a
<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
Nov 13 '05 #5

P: n/a
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
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.