when to use case instead of nested iifs

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

njack217 - 15 Jun 2007 14:20 GMT

ok, I am attempting to do this without a massive amount of queries
each doing a part of it which would be really easy but not "right" and
would result in poor performance I'm sure do to the loading time on
each query etc. What I am trying to do is filter down the results by 2
groups of criteria where they produce a numeric value for each of the
conditions and display the data where the two conditions match...
Sounds more complex than it is really I'll post the sql to give you a
better idea, but my questions are
1. should I be trying to do this in a query or should I use a function
in a control to do it?
2. Should I be using case instead of nested iifs? (I havent had too
much experience with using case and cant find a really good example
that I can aply to this)
Any help would be appreciated!
TIA,

Nate

Reply to this Message

njack217 - 15 Jun 2007 14:22 GMT

wow good job to me forgot to post the sql. here it is:

SELECT
com524_by_part.date, com524_by_part.item, com524_by_part.qty,
com524_by_part.ext_cost, com524_by_part.prod_org,
com524_by_part.pack_slip, com524_by_part.[cno#], cnmb.desc
FROM (com524_by_part INNER JOIN [userdata-global] ON com524_by_part.
[that 1 field] = [userdata-global].[that 1 field]) LEFT JOIN cnmb ON
com524_by_part.[cno#] = cnmb.[cust no]
WHERE
(((IIf([cno#] Like [enter cust no] And [date] Like [enter date] And
[enter item] Like [item],"3",
IIf([cno#] Like [enter cust no] And [date] Like [enter date],"2",
IIf([cno#] Like [enter cust no] And [enter item] Like [item],"2",
IIf([date] Like [enter date] And [enter item] Like [item],"2",
IIf([enter date] Like [date] And [item] Like [enter item],"2",
IIf([enter item] Like [item],"1",
IIf([date] Like [enter date],"1",
IIf([enter cust no] Like [cno#],"1",
Null)))))))))
like
IIf([enter cust no] Is Not Null And [enter date] Is Not Null,"2",
IIf([enter date] Is Not Null And [enter item] Is Not Null,"2",
IIf([enter cust no] Is Not Null And [enter item] Is Not Null,"2",
IIf([enter date] Is Not Null And [enter item] Is Not Null And [enter
cust no] Is Not Null,"3",
"0"))))))
;

Reply to this Message

david@epsomdotcomdotau - 15 Jun 2007 23:56 GMT

It is the LIKE that slows you down, not the IIF

The IIF gets evaluated like a SWITCH anyway.
The only advantage of using SWITCH is that sometimes
it is clearer - sometimes you loose track of your IIF
nesting - but your nested IIFs look clear to me.

Certainly if I had very much data (more than a few hundred
records), I would use a form to build new SQL each time so
that I could evaluate the IIF terms in VB and only put the
minimum number of LIKE terms into my SQL each time.

Also, if you can replace some of the LIKE terms with =,
do that. If you can rewrite the SQL so that some of the
IS NOT NULL tests are done instead of LIKE tests, do
that.

A vba CASE statement is very powerful and flexible, and
rarely you might use a VBA function as your WHERE condition,
but using a VBA function as your WHERE condition is very
inefficient, and SWITCH/IIF conditions work well, so I wouldn't
think that you would want to use a VBA function as your WHERE
condition just because you had a simple SWITCH or cascading
IIF condition.

(david)

> wow good job to me forgot to post the sql. here it is:
>
[quoted text clipped - 24 lines]
> "0"))))))
> ;

Reply to this Message