Rounding to 1 million and formatting it with a comma 1

[emik]

Hello,

I posted this message already in the VBA forum, so I apologize to anyone who has to read this twice (I don't see any way of deleting the post).

Anyways I'm hoping someone in this forum will be able to help.

I need to round a number to the nearest million but show a comma.

For example:

$34,450,000.00 should be $34,5 (I'm able to get it to 345)

I need to also do it inside a query, right now the field value is:

Market: Sum(Round(([MarketValue]/100000)))

I really appreciate any help and I apologize again for double posting.

Thanks.

 

[Golom]

? Replace(Format(34450000.00,"$#,,.0" ),".",",")
$34,5

 

[emik]

Hi Golom,

It works when I use the code you gave me but no in conjunction with how I'm building the value:

Market: Replace(Format(Sum(Round(([MarketValue]/100000))),"$#,,.0"),".",",")

it just returns: $,0

Could you explain to me what that code is doing in more detail?

Thanks.

 

[Golom]

You want

Market: Replace(Format(Sum([MarketValue]),"$#,,.0"),".",",")

The Format statement takes care of the rounding. Dividing by 100000 turns 34450000.00 (for example) into 344.5 and then Format tries to round that to the nearest 100,000 ... which is of course ... zero.

 

[emik]

It worked!

Thank you so much.

 

[emik]

The only thing I see now is when I sort (it needs to be sorted by highest market value). It does not recognize the first 2 digits, so it seems it is looking at the first number and deciding if it is bigger or not.

eg:

$33,9
$4,1
$5,8

 

[PHV]

In the SQL view pane change the order by clause:
ORDER BY Sum([MarketValue])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886