Hello experts,
This is not really a question, but rather a open thread for technical discussion. However, points will be awarded for good explanations and original examples (see below).
I just lost one day hunting a bug in an application I'm working on. I quickly found the origin, but at first failed to correct the bug by introducing a new one. Consider the following query.
UPDATE Table1 SET OK=IIf(Something,True,Fals
e)
The idea: set the OK flag for those records where 'Something' is both not null and not zero. Note that a previous version using Nz() caused the original bug, which I'm not going into. I'm also not expanding on why I would want to store a derived information like this (it had to do with optimization; let's leave it at that).
The problem: IIf(), when interpreted by Jet, does not work *exactly* like VB's IIf(). Since we all know that Eval() implements Jet's expression evaluator, we can test the following from the immediate pane:
? IIf(0.1,True,False)
True
? Eval("IIf(0.1,True,False)"
)
0
For VB, 0.1 is of course 'True', being non-zero. But apparently, 0.1 is 'False' for Jet, meaning that the argument is probably first cast to a Variant Integer (or a Variant Boolean) before being evaluated.
Another such difference between VB and Jet can be discovered by comparing:
? 3 And 7
3
? Eval("3 And 7")
-1
This has already been discussed in a previous Expert thread: {
http:/Q_22135795.html}. Other known differences are that Jet allows single quotes, does not parse VB type-definition characters, and implements additional SQL operators.
So, the questions are:
* Can you explain why IIf() behaves differently between Jet and VB?
* Is this a bug or a feature?
* Do you have other examples of such differences?
The question is *not*:
* How to make this query work as expected?
But if course, feel free to experiment and share if you want.
Cheers all!
Markus
(°v°)
Start Free Trial
