Format a number in ms access 2010

I have an expression in a query that calculates two fields in the query with a syntax of the following:

PPM: [SumofQTY]/[SumOfReceivedQuantity]*1000000

The result it gives me is 193051.717331228

I need it to return a value of 193,052 but do not know how to format this. Can someone provide me the proper syntax for my query
tmaususerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
PPM: Round([SumofQTY]/[SumOfReceivedQuantity]*1000000,0)
0
Jeffrey CoachmanMIS LiasonCommented:
Set the format property of this field to: Standard
Set the decimal places property to: 0
0
Helen FeddemaCommented:
The formatting should be done in a form or report where the calculated value is displayed.  It is generally not a good idea to try to format a calculated number directly in an expression.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

tmaususerAuthor Commented:
PPM: Round([SumofQTY]/[SumOfReceivedQuantity]*1000000,0)
 worked great but how do I get a comma in my number?
0
Gustav BrockCIOCommented:
Round is buggy so to get true 4/5 rounding you should use Format. The string from this converts to a double by CDbl:

PPM: CDbl(Format([SumofQTY]/[SumOfReceivedQuantity]*1000,"0.000"))

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Patrick MatthewsCommented:
Not a challenge, gustav, just a question: is Round truly buggy--that is, does it give answers that do not correctly apply "bankers rounding"--or is it that the Round function works as advertised, but you prefer a different rounding algorithm?

:)
0
Gustav BrockCIOCommented:
Yes, it does Banker's rounding which is OK if you know about it. However, most expect or prefer traditional 4/5 rounding which - strangely - Format as the only native VB(A) function performs.

But Round is buggy (note 2):
http://www.xbeat.net/vbspeed/c_Round.htm

You may run the extensive test here:
http://www.xbeat.net/vbspeed/IsGoodRound.htm

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.