I am trying to truncate double values in a query to 2 decimal places (i.e. round them down). As far as I am aware, there is not a "round down" function in vba, and so I have implemented the following method: - FIX( expression * 100 ) / 100
This, most of the time, works well. However, when I try the expression ( 5 * 85.41 ), which is equal to 427.05, i.e. - fix( 5 * 85.41 * 100 ) / 100
I get 427.04. If I try - Dim a As Double
-
a = 5 * 85.41 * 100
-
MsgBox (Fix(a) / 100)
I get 427.05, which is the correct value.
Something subtle is happening here which I'm not picking up on. Can anyone explain what is happening?
Thanks.
9 3431 NeoPa 32,556
Expert Mod 16PB
As a full member now, you should know that we expect your code to be posted in [code] tags (See How to Ask a Question).
This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.
Please use the tags in future.
ADMIN.
I cant explain why that is happening, but im wondering why you are mulitiplying your expression by 100, only to divided by 100? you would get the same answer if you removed both.
NeoPa 32,556
Expert Mod 16PB
I tried it out and I get the same as you.
I've thought of various answers but none of them seem to be right I'm afraid. I expect it's a rounding (internal calculation) issue, but why it should happen with one approach and not the other is beyond me for the moment.
Let's see what turns up. I may even have an idea myself if I leave it stewing for a while...
NeoPa 32,556
Expert Mod 16PB
I cant explain why that is happening, but im wondering why you are mulitiplying your expression by 100, only to divided by 100? you would get the same answer if you removed both.
That's not right Neekos.
The value is multiplied by 100; then the Fix() function strips off any fractional part; THEN the result of that is divided by 100.
Here's my theory.
This works correctly: - Debug.Print Fix(CDbl(5 * 85.41 * 100))
What's happening is when the processor calculates the value as a double but is not assigning, i.e. converting, it to a double, it stores the number as the lowest amount of bits required. However, the Fix expression converts that back to a double.
Doubles don't have absolute precision. So when it gets calculated, it's something like 42705.0000000000001. When stored to a double, that's what is stored. But instead it's passing 42705 at the lowest parity possible. When that gets converted to a double, it becomes 42704.9999999999999999999.
That's what I think is happening anyways.
Here's my theory.
This works correctly: - Debug.Print Fix(CDbl(5 * 85.41 * 100))
Great! That is what is happening, I'm sure. However, if I try the following in a query field: -
Value: Fix(CDbl([Quantity]*[AssetPrices].[Price]*[AssetPrices_1].[price]*100))/100
-
Where:
[Quantity] = 5
[AssetPrices].[Price] = 85.41
[AssetPrices_1].[price] = 1
CDbl does not seem to have an affect as the number returned is 427.04, not 427.05. Does CDbl not work in a query?
CDbl is a conversion function for VBA data types. Try creating a public function to do the calculation and call that function from SQL instead.
CDbl is a conversion function for VBA data types. Try creating a public function to do the calculation and call that function from SQL instead.
Yep, that was the option I went for. It slows down the query a bit but it works. I still had some trouble with using CDbl and so in the end I decided to add on a small amount to force the rounded value to have a decimal greater than 0. E.g. - Int(expression * 100 + 0.000001) / 100
Thanks for your help.
Not a problem, good luck.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: domeceo |
last post by:
can anyone tell me why I cannot pass values in a setTimeout function
whenever I use this function it says "menu is undefined" after th
alert.
function imgOff(menu, num) {
if (document.images) {...
|
by: phil_gg04 |
last post by:
Dear Javascript Experts,
Opera seems to have different ideas about the visibility of Javascript
functions than other browsers. For example, if I have this code:
if (1==2) {
function...
|
by: laredotornado |
last post by:
Hello, I am looking for a cross-browser way (Firefox 1+, IE 5.5+) to
have my Javascript function execute from the BODY's "onload" method,
but if there is already an onload method defined, I would...
|
by: sushil |
last post by:
+1 #include<stdio.h>
+2 #include <stdlib.h>
+3 typedef struct
+4 {
+5 unsigned int PID;
+6 unsigned int CID;
+7 } T_ID;
+8
+9 typedef unsigned int (*T_HANDLER)(void);
+10
|
by: Olov Johansson |
last post by:
I just found out that JavaScript 1.5 (I tested this with Firefox 1.0.7
and Konqueror 3.5) has support not only for standard function
definitions, function expressions (lambdas) and Function...
|
by: Beta What |
last post by:
Hello,
I have a question about casting a function pointer. Say I want to make
a generic module (say some ADT implementation) that requires a function
pointer from the 'actual/other modules'...
|
by: f rom |
last post by:
----- Forwarded Message ----
From: Josiah Carlson <jcarlson@uci.edu>
To: f rom <etaoinbe@yahoo.com>; wxpython-users@lists.wxwidgets.org
Sent: Monday, December 4, 2006 10:03:28 PM
Subject: Re: ...
|
by: Larax |
last post by:
Best explanation of my question will be an example, look below at this
simple function:
function SetEventHandler(element)
{
// some operations on element
element.onclick =
function(event)
{
|
by: alex |
last post by:
I am so confused with these three concept,who can explained it?thanks
so much?
e.g.
var f= new Function("x", "y", "return x * y");
function f(x,y){
return x*y
}
var f=function(x,y){
|
by: VK |
last post by:
I was getting this effect N times but each time I was in rush to just
make it work, and later I coudn't recall anymore what was the original
state I was working around. This time I nailed the...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |