[AccessD] IIf in query discussion

Well said.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Brett Barabash
Sent: Monday, March 31, 2003 2:47 PM
To: 'accessd at databaseadvisors.com'
Subject: RE: [AccessD] IIf in query discussion


I have heard the argument that IIf is too slow, but in actual practice
haven't seen much of a performance degradation.  ADH says to avoid them in
code when using functions as the truepart or falsepart arguments, because
both of them fire everytime, regardless of the condition.  For example:

If x = 1 Then
    y = DoThis()
Else
    y = DoThat()
End If

Only DoThis will fire if x = 1.

However, translated to an IIf:

y = IIf(x = 1, DoThis(), DoThat())

DoThis() AND DoThat() will both fire!  Not only is this a performance hog,
but it can also result in an error condition in this case if DoThat() blows
up when x = 1.

On the other hand, I see no value in replacing IIf in a Query with VBA code.
IIf is part of the VBA compiled C libraries, and should execute far quicker
than a VBA function (just guessing, haven't tested it).  Nested IIfs?  I
would use the custom function, if for no other reason than readability.


-----Original Message-----
From: Chris Mackin [mailto:chris at denverdb.com]
Sent: Monday, March 31, 2003 2:22 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] IIf in query discussion


One alternative is to create a Public Function that returns what you need,
probably using either an If..Then or a Select Case statement.  From a
programming standpoint I think nested IIF's are a nightmare to read, so
anything beyond one or two levels of nesting gets turned into a Function.

Chris Mackin
Denver Database Consulting, LLC
www.denverdb.com


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Michael R
Mattys
Sent: Monday, March 31, 2003 1:08 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] IIf in query discussion


I may learn something new here.

What are the alternatives?
Functions with paramarray variables?
More precise SQL statements?
Else?

Michael R. Mattys
www.mattysconsulting.com

----- Original Message -----
From: "John Bartow" <john at winhaven.net>
To: <accessd at databaseadvisors.com>
Sent: Monday, March 31, 2003 3:14 PM
Subject: RE: [AccessD] IIf in query discussion


> MessageYou probably need to give us more details but I tend to agree in
> philosophy with the idea that IIF should not be used unless necessary. But
> in practice it is sometimes necessary. I have it used it mostly when
> querying a pre-existing database which is poorly normalized (hmmm, that
> might be an oxy-moron). I use it as a guideline not a rule though, the
only
> "rule" I tend to have is to never rule out anything.
>
> I think the biggest drawback beyond speed is that its Access specific and
> they can be difficult to read. I think "spreadsheet people" love it
though.
>
> JB
>
>  -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]
> Sent: Monday, March 31, 2003 1:39 PM
> To: accessd at databaseadvisors.com
> Subject: [AccessD] IIf in query discussion
>
>
>   Recently I read an article by an Access expert who suggested that using
> 'iif' in a query is a rookie mistake.
>
>   I've made that mistake.
>
>   My question: Does using 'iif' in a query just slow done processing, or,
> does it have more substantial consequences?
>
>   TIA,
>   Myke
>
>


----------------------------------------------------------------------------
----


> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com