[AccessD] Range of Values in Access Switch Function

Stuart,

WOW... you AccessD folks are great!

I never thought about this approach.

We may or may not use this method, but it is nice to have another approach in our bag of tricks for future situations.

Thanks for your help,
Brad
   


-----Original Message-----
From: accessd-bounces at databaseadvisors.com on behalf of Stuart McLachlan
Sent: Thu 9/16/2010 5:25 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Range of Values in Access Switch Function
 
If the "real" function is much more complicated, I'd roll my own along the lines of:

Function DerivedGroup(Product as long) as String
Sselect Case Product
    Case 1 - 5 
         DerivedGroup = "ProductGroupOne"
    Case 5,6,9
         DerivedGroup = "ProductGroupTwo"
    Case 7
         DerivedGroup = "ProductGroupThree"
...
     Case else
         DerivedGroup =  "Default Product Group"
End Select
End Function

-- 
Stuart

On 16 Sep 2010 at 16:28, Brad Marks 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
> 


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