[AccessD] WHERE Clause

[Heenan, Lambert]

My short and useless answer to your question is 'probably not', but thanks for this curious tip.

I've always used IN()  in the form...

	[Some Field] In(1,2,3,4) - i.e. in a list of possible values.

Never know you could do...

	Some Field Value In(F1,F2,F3) - i.e. find the value in a list of fields


Lambert

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Tuesday, July 21, 2015 4:26 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] WHERE Clause

So I have to create a bunch of similar reports, that really only differ in the where clause.

I took an existing query such as:

SELECT * FROM SomeQuery WHERE [EligStatus] = 'OHC' OR [PrevEligStatus] = 'OHC'

And turned it into

SELECT * FROM SomeQuery WHERE 'OHC' IN ([EligStatus], [PrevEligStatus])

This made it easy to loop through a recordset of WHERE clauses and run the same report and only having to change the first part of the WHERE clause



I likewise turned the following from:
SELECT * FROM SomeQuery
WHERE (PrevEligStatus='MCE'
AND Eligstatus IN ('HF', 'MCal', 'Blank', 'Limited', 'NoElig')) OR (EligStatus='MCE'
AND PrevEligstatus IN ('HF', 'MCal', 'Blank', 'Limited', 'NoElig'))

to:
SELECT * FROM SomeQuery
WHERE 'MCE' IN (EligStatus, PrevEligStatus) AND (Eligstatus IN ('HF', 'MCal', 'Blank', 'Limited', 'NoElig') OR PrevEligstatus IN ('HF', 'MCal', 'Blank', 'Limited', 'NoElig'))

is there a shorter/more efficient way of writing this? Something like:
(Yes, I know the following doesn't work) SELECT * FROM SomeQuery WHERE 'MCE' IN (EligStatus, PrevEligStatus) AND ((Eligstatus OR PrevEligstatus) IN ('HF', 'MCal', 'Blank', 'Limited', 'NoElig'))
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com