Hi
That is because there are two individuals sharing the same name, iif. In
Jet-SQL, iif is a statement, while in VBA, iif is a function. " If then
elseif else endIf ", in VBA is a (complex) statement. A statement is like a
control of the flow of execution. Not all parts of the control flow need to
be evaluated (like an unconditional GO TO that skips other statements and
functions ). A function, on the other hand, always evaluate its argument
even before beginning its own execution. As example, with
Abs( 4 + 5 )
the internal working of Abs will just see 9, not 4+5, and would not be able
to differentiate that 9 from another 9 coming from 12-3, as example. Abs( )
is a function. All the arguments are evaluated, then, the function is
called. You can trace that with
MyFunction3( MyFunction1() + MyFunction2( ) )
and you will see that MyFunction1 and MyFunction2 are called before
MyFunction3.
iif( ) - in VBA - is a function, so, each argument have to be evaluated
before iif starts. That is not a "special" rule, it is the general rules for
all functions.
iif( ) - in Jet SQL - is a statement, we cannot really speak of "argument"
here, but definitively, just the required "parts" are evaluated, as in any
other statements.
Choose( ) and Switch( ) are only defined in VBA, not in Jet SQL, and are
functions, not statements. As a side effect, you cannot use them from a C++
with Jet, or VB6 with Jet, just from a Access+Jet combo.
Hoping it may help,
Vanderghast, Access MVP
"Trevor Best" <bouncer@localhost> wrote in message
news:hf********************************@4ax.com...
On Mon, 29 Sep 2003 08:24:14 -0400 in comp.databases.ms-access,
"Michel Walsh" <va*********@msn.com> wrote:
Hi,
Alternatively:
WHERE Choose( Forms!MyForm!MyCbx, (Inventory-ReorderPoint) <0,
(Inventory-ReorderPoint)=0, (Inventory-ReorderPoint) BETWEEN 1 and 3,
(Inventory-ReorderPoint) Between 1 and 6 )
Note that Choose is a function, not a statement, and thus, each of
itsargument need to be evaluated.
I'm not bothering to check there but are you sure? IIf() is a function
and when used in VBA all parameters are evaluated but if used in an
expression in a form or query it doesn't evaluate all parameters, it
works differently to VBA.
--
A)bort, R)etry, I)nfluence with large hammer.