Solved

Calculate values in query with 2 decimals

Posted on 2014-02-12
9
192 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
 
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:timohorn
ID: 39853059
@ed


No same results :-((
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 500 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 49

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

863 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

22 Experts available now in Live!

Get 1:1 Help Now