[AccessD] WHERE Clause

[David McAfee]

Thanks Lambert.

For some reason I was thinking there was some way using true or false.

D
On Jul 21, 2015 1:43 PM, "Heenan, Lambert" <Lambert.Heenan at aig.com> wrote:

> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>