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