[AccessD] WHERE Clause

David McAfee davidmcafee at gmail.com
Tue Jul 21 15:25:44 CDT 2015


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'))


More information about the AccessD mailing list