Solved

Format a number in ms access 2010

Posted on 2012-03-16
7
757 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Library not Registered 16 50
ms access 2013, running .mdb 2 31
linked subforms are yielding error:  ... (800110108) 3 16
Error: Operation must use an updateable query 2 11
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now