x
Solved

# Format a number in ms access 2010

Posted on 2012-03-16
Medium Priority
763 Views
I have an expression in a query that calculates two fields in the query with a syntax of the following:

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
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

LVL 77

Assisted Solution

peter57r earned 200 total points
ID: 37730602
0

LVL 74

Expert Comment

ID: 37730606
Set the format property of this field to: Standard
Set the decimal places property to: 0
0

LVL 31

Expert Comment

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

Author Comment

ID: 37730643
worked great but how do I get a comma in my number?
0

LVL 52

Accepted Solution

Gustav Brock earned 200 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:

/gustav
0

LVL 93

Expert Comment

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 52

Expert Comment

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

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
###### Suggested Courses
Course of the Month15 days, 8 hours left to enroll