[AccessD] WHERE Clause

[Bill Benson]

Sometimes I string all the values together pipe separated,  and say where
'|LONG|LIST|OF|FIELD|VALUES|' Like "*|" & [MyField] & "|*

On Jul 21, 2015 4:27 PM, "David McAfee" <davidmcafee at gmail.com> wrote:

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