Query Optimization

[Dwight]

Hello:

I have read in Access Help that using IIF statements in queries does not allow the query to be optimized and can be a cause for slow performance.

Simple question: If I create a public function to do my IIF statment operation will this allow the query to be optimized and run at maximum speed?

Just curious,

Dwight

 

[Len Boorman]

No real idea if Public function will have effect. Can opnly say that if you are using lots of Iff statements then I would critically review what you are trying to do. If it is just the odd Iff then will you notice the difference ?.

L

 

[Dwight]

Thanks. No, I don't have too many IIF statements. Like you suggested I try to avoid using them.

In this instance, my users enter buys and sells as positive values. To get the total I make the sells negative with an IIF and sum them up. Then I do some other things and it becomes a nested query situation.

And since one query has an IIF none of the queries in the series can be optimized. It's no big deal.

 

[mresann]

Depending on how many fields use the Iif statements, and how many records you are returning from the query, it is sometimes more efficient to use a module. Keep in mind that the Iif statement, which you must use when you do not use VBA code, will ALWAYS analyze both conditions. When you use code, using hte If-Then statement procedure will only analyze the first equation if it is true, and only will go to the second equation if the first condition is false. One should rarely use Iif statements in VBA code for this very reason.

When you have several conditions that require nested Iif statements, it can become even more inefficient. In code, I use Select Case statements when I need more than two conditions in order of expected frequency, but that's another thread.