IIF in WHERE???

[danb]

Hey, I seem to be having little joy with my previous detailed posting, so I'll try something a bit more concise...

Can I put an iif statement in a 'where' clause, such that if a value is passed into a query from an ASP page it can dynamically decide what to include within the 'where' clause?

e.g.

Select * from Shops WHERE IsActive=true
IIf SellsBooks_IN=true then AND SellsBooks=true
IIf SellsClothing_IN=true then AND SellsClothing=true

If there's any way this can be done, I'd much appreciate some help!

 

[fuzzygeek]

Dynamic SQL -- Knowledge Base Article

I know you said that you didn't want to use vba, but you might look at this link.

http://support.microsoft.com/default.aspx?scid=kb;en-us;304302&Product=acc2002

 

[Pat Hartman]

You will find that the help you get here is better if you stick to a single thread. Also keep in mind that people don't just sit around waiting to answer your questions. The members come and go. Sometimes you'll get an answer within 5 minutes. Other times it can take hours. Be a little patient and give it more than 8 minutes next time before jumping in with another post.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=55217

 

[EMP]

It's not clear whether you want to join the options with "AND" or "OR".

For instance, if someone selected Clothing and Books, do you want to return "Active shops which must sell both Clothing AND Books" or "Active shops selling either Clothing OR Books OR both"? though most likely you will want the OR condition.


For the AND condition, you can try

SELECT Shops.*
FROM Shops
WHERE IsActive=-1 And IIf(SellsClothing_IN=-1, SellsClothing=-1, True) and IIf(SellsBooks_IN=-1, SellsBooks=-1, True) and IIf(SellsFood_IN=-1, SellsFood=-1, True);


For the OR condition, you can try

SELECT Shops.*
FROM Shops
WHERE IsActive=-1 And (IIf(SellsClothing_IN=-1, SellsClothing=-1, false) or IIf(SellsBooks_IN=-1, SellsBooks=-1, false) or IIf(SellsFood_IN=-1, SellsFood=-1, false));

 

[danb]

Thanks very much EMP! It was the syntax I was struggling with (erm... mostly).

I should be able to do what I'm wanting now. You're a life saver!

 

[namliam]

Dont use an IIF for something in a where clause... I am sure if you think about it you can do it all in a regular where...

Select * 
from Shops 
WHERE IsActive=true 
AND (( SellsBooks_IN=true AND SellsBooks=true )
     OR (SellsClothing_IN=true AND SellsClothing=true ))

That should do what you want (from your sample)

Regards

 

[EMP]

Yes. It's quite true that you can do it all in a regular where.

For the OR condition, it's quite simple.

SELECT *
FROM Shops
WHERE IsActive=True And
((SellsBooks_IN=True And SellsBooks=True)
Or (SellsClothing_IN=True And SellsClothing=True)
Or (SellsFood_IN=True And SellsFood=True));


For the AND condition, it's not so simple, as you have to OR seven groups together.

SELECT *
FROM Shops
WHERE IsActive=True And
((SellsBooks_IN=True And SellsBooks=True And SellsClothing_IN=False And SellsFood_IN=False)
Or (SellsBooks_IN=False And SellsClothing_IN=True And SellsClothing=True And SellsFood_IN=False)
Or (SellsBooks_IN=False And SellsClothing_IN=False And SellsFood_IN=True And SellsFood=True)
Or (SellsBooks_IN=True And SellsBooks=True And SellsClothing_IN=True And SellsClothing=True And SellsFood_IN=False)
Or (SellsBooks_IN=False And SellsClothing_IN=True And SellsClothing=True And SellsFood_IN=True And SellsFood=True)
Or (SellsBooks_IN=True And SellsBooks=True And SellsClothing_IN=False And SellsFood_IN=True And SellsFood=True)
Or (SellsBooks_IN=True And SellsBooks=True And SellsClothing_IN=True And SellsClothing=True And SellsFood_IN=True And SellsFood=True));

 

[Rose412]

Dont use an IIF for something in a where clause

I use IIF a lot in where clauses and select clauses. Is there any reason against this? Any disadvantages that I should know about? I tried looking this up in the Access books that I have, but no joy.

I could only find this in the Access help file:

IIf always evaluates both truepart and falsepart, even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating falsepart results in a division by zero error, an error occurs even if expr is True.


It seems to be only a warning, not an advice against using it.

Thanks and regards.

 

[Mile-O]

If you use it in a query or code you run the risk of seriously slowing down the speed of your database application. If you have loads of records and even just one IIf() statement you might begin to notice considerable speed reduction. The more you add, the more coffee you'll have while you wait for a query to run.

 

[Rose412]

Thanks.

In EMP's two queries for the AND condition:

SELECT *
FROM Shops
WHERE IsActive=True And
((SellsBooks_IN=True And SellsBooks=True And SellsClothing_IN=False And SellsFood_IN=False)
Or (SellsBooks_IN=False And SellsClothing_IN=True And SellsClothing=True And SellsFood_IN=False)
Or (SellsBooks_IN=False And SellsClothing_IN=False And SellsFood_IN=True And SellsFood=True)
Or (SellsBooks_IN=True And SellsBooks=True And SellsClothing_IN=True And SellsClothing=True And SellsFood_IN=False)
Or (SellsBooks_IN=False And SellsClothing_IN=True And SellsClothing=True And SellsFood_IN=True And SellsFood=True)
Or (SellsBooks_IN=True And SellsBooks=True And SellsClothing_IN=False And SellsFood_IN=True And SellsFood=True)
Or (SellsBooks_IN=True And SellsBooks=True And SellsClothing_IN=True And SellsClothing=True And SellsFood_IN=True And SellsFood=True));


SELECT Shops.*
FROM Shops
WHERE IsActive=-1 And IIf(SellsClothing_IN=-1, SellsClothing=-1, True) and IIf(SellsBooks_IN=-1, SellsBooks=-1, True) and IIf(SellsFood_IN=-1, SellsFood=-1, True);


Would the first one with so many And's and Or's run faster than the second one? Would And's and Or's slow things down? The second one is surely easier to write.

 

[EMP]

Rose,

I think it's difficult to tell unless you create a test table with a lot of records and test the queries.

But it's always good to know that there are alternative ways of doing some thing, for when you are not satisfied with one method, you have another one at least to try.

EMP

 

[Rose412]

Thanks. I feel much relieved!

When I first read this thread and found that some people were against using IIF in criteria, I thought I had done something wrong.

I have a search form on which the users can simultaneously search from 1 to 5 fields using AND. I used five IIF's in the criteria of the query. It runs fine without any problems and I am happy with it.

 

[EMP]

These are open forums. People can freely give their own opinions.  But saying "Don't do this" without giving a reason can make those who have been using that particular method worried.

If you do a search on these forums, you will see that many people use IIF in the criteria of their queries.  Even the one who said "Don't do this" had had more than one posts telling people how to write IIF expressions in criteria.  Since you are happy with the query you are using, you don't have to change it.