Switch Statement in WHERE Clause

gray

Registered User.
Local time
Today, 21:22
Joined
Mar 19, 2007
Messages
578
Hi

Been trying to guess this for hours...

I want to count the number of records returned in a SELECT subquery and alter the WHERE of the main query accordingly...I know this should be possible.. So in English... (System_Record is a Boolean)...

if records returned by SELECT = 0 then set the WHERE condition " OR System_Record=TRUE"

if records returned by SELECT > 0 then set the WHERE condition " AND System_Record=FALSE"

My code so far.. don't laugh! The QBE did! :)
Code:
SELECT 
TBL.Item_Name,
TBL.Unique_No,
TBL.ID, 
(SELECT COUNT(UNIQUE_NO) FROM ADDRS_TBL AS TBL1 
WHERE TBL1.ID=75666) As My_Count 
FROM ADDRS_TBL AS TBL 
WHERE (TBL.ID=75666) 
SWITCH(My_Count=0, " OR System_Record=True", My_Count>0, " AND System_Record=False")
Yikes!! Can someone help please?
 
Last edited:
Code:
SELECT 
TBL.Item_Name,
TBL.Unique_No,
TBL.ID, 
(SELECT COUNT(UNIQUE_NO) FROM ADDRS_TBL AS TBL1 
WHERE TBL1.ID=75666) As My_Count 
FROM ADDRS_TBL AS TBL 
WHERE (TBL.ID=75666 OR System_Record=True AND MyCount=0)  OR  (TBL.ID=75666 AND My_Count>0 AND System_Record=False)
 
Hi

Thanks for the reply Spike... For some reason the re-jigged query generates an 'Enter Parameter' box asking for a value for My_Count... this is with both a valid and invalid ID=xxx value... I tried adding the table alias to all the WHERE clauses as in

WHERE (TBL.ID=75666 OR TBL.System_Record=True AND TBL.My_Count=0) OR (TBL.ID=75666 AND TBL.My_Count>0 AND TBL.System_Record=False)

but it still prompts for a My_Count Value.... But I'm sure using My_Count in the main-WHERE is legal..
 
Hmm there is no such thing as TBL.My_Count which is why you get a prompt. Try My_COunt on its own.

Ok, you DID try that....
 
Last edited:
I tried both Spike... it prompts either way... it seems the main WHERE clause can't find the "made-up" column, My_Count, ... I'm sure this is a legal thing to do..
 
You are sure, but Access isn't. And it wins :-)

While you wait for more qualified input, check the SQL EXISTS clause - google.

Afterall, you are only using MyCount to determine whether the specified records exist or not.
 
Just spotted a thread that says Access does not recognise aliases in the WHERE clause... I'm a bit amazed at that? So probably some torturous self join is needed?
 
Hi

Yes I dabbled with EXISTS earlier but the problem I had was similalry passing the result of that into the WHERE side (I didn't know it was illegal in Access then) ...

Or I guess I could move it to the WHERE side and try to use some Boolean magic to amalgamate it with yours...
 
Or I guess I could move it to the WHERE side and try to use some Boolean magic to amalgamate it with yours.

Exactly - (and here are some more characters, to make the minimum 10 for the forum to accept this post!!"
 
Hi

Thanks for the EXISTS tip Spike!...

For anyone else reading, this seems to work for me....

Code:
SELECT 
TBL.Item_Name,
TBL.Unique_No,
TBL.ID,
TBL.System_Record
FROM Addrs As TBL 
WHERE (TBL.ID=75666 AND EXISTS( SELECT * FROM ADDRS As TBL1 WHERE TBL1.ID=75666)) OR (System_Record=True AND NOT EXISTS( SELECT * FROM ADDRS As TBL1 WHERE TBL1.ID=75666) )

When an invalid ID is used, my system_record is retrieved... when a valid one is used results are as expected...
 

Users who are viewing this thread

Back
Top Bottom