424,456 Members | 1,250 Online
Need help? Post your question and get tips & solutions from a community of 424,456 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
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 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 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.