[AccessD] Range of Values in Access Switch Function

Don't use complicated switch statements in queries.  The overhead is
just too high, and the performance takes a major hit if you're dealing
with a large number of records.  You'd be better off creating a custom
function to return the answer using a case statement and calling that
function in the query.

Charlotte Foust

On Thu, Sep 16, 2010 at 2:28 PM, Brad Marks <BradM at blackforestltd.com> wrote:
> Steve,
>
> Thanks for the example.  This works nicely in the query.
>
> The reason that I am experimenting with the Switch Function instead of
> the IIF approach is because of the complexity of the "real" query.  The
> example that I posted is just a small part of a much bigger and more
> complicated SWITCH statement.
>
> Thanks again for the assistance.
> Brad
>
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Steve Schapel
> Sent: Thursday, September 16, 2010 4:18 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Range of Values in Access Switch Function
>
> Brad,
>
> How about this...
> [Product] >= "1" And [Product] <= "5"
>
> I am curious about why you would prefer Switch() in this instance,
> rather
> than IIf().
>
> Regards
> Steve
>
> -----Original Message-----
> From: Brad Marks
> Sent: Friday, September 17, 2010 8:34 AM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Range of Values in Access Switch Function
>
> Is it possible to specify a range of values in a Switch Function that is
> used in an Access query?
>
>
> I have a Switch Function like this sample -
>
>
> DerivedGroup: Switch([Product] In ("1","2","3","4","5"),"Product Group
> One",True,"Default Product Group"))
>
>
> I would like to specify a range rather than spelling out each possible
> value
>
>
> Something like   =("1" through "5")
>
>
> Thanks for your help,
> Brad
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>