Solved

Format a number in ms access 2010

Posted on 2012-03-16
7
759 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:tmaususer
7 Comments
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 50 total points
ID: 37730602
PPM: Round([SumofQTY]/[SumOfReceivedQuantity]*1000000,0)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37730606
Set the format property of this field to: Standard
Set the decimal places property to: 0
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 37730641
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:tmaususer
ID: 37730643
PPM: Round([SumofQTY]/[SumOfReceivedQuantity]*1000000,0)
 worked great but how do I get a comma in my number?
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 50 total points
ID: 37732162
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 37733578
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37733712
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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question