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?

Improve company productivity with a Business Account.Sign Up

x
 
Gustav BrockConnect With a Mentor CIOCommented:
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
 
peter57rConnect With a Mentor Commented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
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
 
tmaususerAuthor Commented:
PPM: Round([SumofQTY]/[SumOfReceivedQuantity]*1000000,0)
 worked great but how do I get a comma in my number?
0
 
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
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.

All Courses

From novice to tech pro — start learning today.