Curious behavior of Access.Application.Eval()

Question

Explain why Access.Application.Eval() (commonly abbreviated as Eval()) produces a different result than just evaluating the original expression in this case:

Debug.Print Round(.575 * 100)
 57 
Debug.Print Eval("Round(.575 * 100)")
 58

EDIT: To address GSerg's answer, the following still returns different results:

Debug.Print Eval("Round(CSng(.575) * 100)")
 57 
Debug.Print Round(CSng(.575) * 100)
 58

For the same reason `Debug.Print Round(0.575#*100.0#), Round(57.5#*1.0#)` returns `57 58`? — Jean-François Corbett, Jun 22 '11 at 14:02

HansUp · Accepted Answer · 2011-06-22T15:33:14.857

6

That multiplication returns a different product under Eval(), but I don't understand why.

Debug.Print (.575 * 100) < 57.5
True

Debug.Print Eval("(.575 * 100) = 57.5")
-1

In the first case, the product is less than 57.5, so Round() will round it down to 57.

In the second case, the product is equal to 57.5, so Round() will apply its standard "round to even" approach to yield 58.

Edit: You all are right that Eval() coerces the literal value to a different data type.

? TypeName(.575)
Double

? Eval("TypeName(.575)")
Decimal

? Round(CDec(.575) * 100)
 58

edited Jun 22 '11 at 15:33

answered Jun 22 '11 at 15:13

HansUp

95,961
11
77
135

As @GSerg surmised I'm guessing it has to do with coercing literal numbers to different datatypes. I'm thinking VBA coerces to Double and Jet coerces to Currency. See the answer I posted. Also, the binary/floating point representation of .575 works out to 0.57499999999999996, which is probably why `(.575 * 100) < 57.5`. – mwolfe02 Jun 22 '11 at 15:20
Good. But what does Jet have to do with this? Are you saying Eval() runs its expression through Jet? I don't see anything about that in the Help topic. – HansUp Jun 22 '11 at 15:30
You're right, Eval() apparently uses the Access Expression Service which is different from (though probably closely tied to) the Jet Expression Service ([source](http://dbaspot.com/ms-access/245024-questions-about-functions-expression-service.html#post986081)). The whole situation is a bit of a black box to me and I'd like to know a bit more about what is going on inside. – mwolfe02 Jun 22 '11 at 15:51
2

I just reread the help for Eval() and this caught my eye: *You can use the **Eval** function to access expression operators that aren't ordinarily available in Visual Basic. For example, you can't use the SQL operators **Between...And** or **In** directly in your code, but you can use them in an expression passed to the **Eval** function.* Since `Between...And` and `In` are expressions that Jet knows how to evaluate, I'm thinking that Jet is involved somehow, either directly or indirectly. – mwolfe02 Jun 22 '11 at 16:01
We have a winner!!! I didn't think to use TypeName to test my theory about literal coercion. Nice one! – mwolfe02 Jun 22 '11 at 16:04
1

Earlier I wrote @GSerg's answer was the most plausible at the time... But this one is more than plausible, it's proof definitive! +2 – Jean-François Corbett Jun 23 '11 at 14:05

score 4 · Answer 2 · answered Jun 22 '11 at 13:58

4

That is because of different float precision.

In one case the constants get recognized as Doubles, in the other as Singles.

? math.round((.575! - Int(.575!)) * 100)
 58 
? math.round((.575# - Int(.575#)) * 100)
 57

answered Jun 22 '11 at 13:58

GSerg

76,472
17
159
346

Ok, so why does this happen: `Print Round(0.575#*100.0#), Round(57.5#*1.0#)` returns `57 58`. It's all doubles. I guess I need to read more on floating-point arithmetic... – Jean-François Corbett Jun 22 '11 at 14:04
@Jean Yeah, with floating points `0.575#*100.0#` might not be exactly equal to `57.5#*1.0#`. As for the question, the following is also true: `math.Round(.575# * 100#) => 57`, `math.Round(.575! * 100!) => 58`. – GSerg Jun 22 '11 at 14:09
This is the most plausible explanation. I'm satisfied. +1 – Jean-François Corbett Jun 22 '11 at 14:13
1

@Jean: It is because .5 can be exactly represented with binary fractions (ie, .1 in binary or 1/2) while .575 cannot. See [Floating Point Arithmetic: Issues and Limitations](http://docs.python.org/tutorial/floatingpoint.html) in the Python tutorial for a concise and straightforward explanation. – mwolfe02 Jun 22 '11 at 14:16
If you enter .5 at a python interpreter it returns 0.5. If you enter .575 it returns 0.57499999999999996. – mwolfe02 Jun 22 '11 at 14:19
2

This was probably the most helpful answer (in terms of pointing everyone in the right direction), but @HansUp's use of TypeName provided a better proof of what was actually going on. – mwolfe02 Jun 22 '11 at 16:08

score 2 · Answer 3 · answered Jun 22 '11 at 15:14

2

GSerg got me thinking. I'm starting to believe that Jet attempts to coerce decimal literals to the Currency type when Eval is called whereas VBA coerces decimal literals to the Double type. Case in point:

? Math.Round(.575 * 100)
 57 
? Math.Round(CSng(.575) * 100)
 58 
? Math.Round(CDbl(.575) * 100)
 57 
? Math.Round(CCur(.575) * 100)
 58 

? Eval("Round(.575 * 100)")
 58 
? Eval("Round(CSng(.575) * 100)")
 57 
? Eval("Round(CDbl(.575) * 100)")
 57 
? Eval("Round(CCur(.575) * 100)")
 58

answered Jun 22 '11 at 15:14

mwolfe02

23,787
9
91
161

2

Nice experiment, but you forgot to coerce the 100! `? Eval("Round(CSng(.575) * CSng(100))")` returns `58`. Anyway, +1 for doing some fairly extensive testing before posting an answer -- something that just isn't common enough on SO. – Jean-François Corbett Jun 23 '11 at 06:38

score 1 · Answer 4 · answered Jun 22 '11 at 13:53

I don't use Access, but I would guess that the Eval evaluates the expression as an Access expression, so that the Round and Int functions may operate differently from the VBA versions?

I know from bitter experience that VBA's Round() function uses the round-to-even rounding method (aka Banker's rounding), rather than the more common round-away-from-zero-on-a-5 (aka symmetric arithmetic rounding) method.

score 0 · Answer 5 · answered Jun 22 '11 at 13:58

0

If you run the following SQL expression in Access, you get 58:

select Round((.575 - Int(.575)) * 100) as MyValue

If you run the following statement in Access (and, for that matter any Office VBA IDE) Immediate window, you get 57:

Round((.575 - Int(.575)) * 100)

So, that leads me to believe VBA has a different way of doing Round rather than Access and, probably more applicable, JET.

Now, why is that different? Dunno...will take someone with better skills than me.

answered Jun 22 '11 at 13:58

ray

8,521
7
44
58

3

This is not the issue: in both the OP's cases, VBA's `Round` is called. You are onto something: VBA's `Round` uses banker's rounding, i.e. round 0.5's to the nearest even integer; while Access does not. But again, this isn't the issue in this particular question. – Jean-François Corbett Jun 22 '11 at 14:07

Curious behavior of Access.Application.Eval()

5 Answers5

Linked