?
Solved

Calculate values in query with 2 decimals

Posted on 2014-02-12
9
Medium Priority
?
203 Views
Last Modified: 2014-02-17
I have a update query where i got 4 figures, 2 with 2 decimals an two with more than two decimals. When i sum the four i get a figure which  differences from the total of round numbers

[SubtotaalHoog]+[SubtotaalLaag]+[BTWhoog]+[BTWlaag]

65,75 + 72,60 + 13,8075 + 4,356 = > 156,51
65,75 + 72,60 + 13,81 + 4,36 = > 156,52

how can i make the query so it will give tho last result
0
Comment
Question by:timohorn
  • 4
  • 3
  • 2
9 Comments
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 39852880
Hello timohorn,

Try to use the one below:


Round([SubtotaalHoog],2)+Round([SubtotaalLaag],2)+Round([BTWhoog],2)+Round([BTWlaag],2)

Sincerely,

Ed
0
 

Author Comment

by:timohorn
ID: 39852884
Thanxs Ed,

tried that already, but no luck, it gives the same amount ;-(
also tried ccur, but no success either.
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 39852998
You may try similar with this one:

Total1: Int(100*[Val1])/100+Int(100*[Val2])/100+Int(100*[Val3])/100

I tested it and it worked for me. I hope it will work with you as well.

Ed
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 39853007
In your query, you may have:

Int(100*[SubtotaalHoog])/100+Int(100*[SubtotaalLaag])/100+Int(100*[BTWhoog])/100+Int(100*[BTWlaag])/100

Sincerely,

Ed
0
 

Author Comment

by:timohorn
ID: 39853059
@ed


No same results :-((
0
 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 1500 total points
ID: 39853194
Use Format and CCur to make a traditional 4/5 rounding:

CCur(Format(65.75, "0.00")) + CCur(Format(72.60, "0.00")) + CCur(Format(13.8075, "0.00")) + CCur(Format(4.356, "0.00"))

=> 156.52

/gustav
0
 

Accepted Solution

by:
timohorn earned 0 total points
ID: 39853225
solved it with a work around, first i update the BTW field to two decimals, and then run the query, works fine.

Thanxs
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 39853321
The reason is that Access normally does Banker's Rounding which is not was many are "hoping for".
The only exception is Format which, however, returns a string, thus the result(s) must be converted to numeric value(s) as the second step.

/gustav
0
 

Author Closing Comment

by:timohorn
ID: 39864296
Not quit what i was hoping for but works in the end ...
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

615 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