Solved

Format a number in ms access 2010

Posted on 2012-03-16
7
761 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

 

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 50

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 50

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

Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

696 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