[AccessD] Query Problem

Todd, Just got back on here today and saw your solution. Works great.
Like the idea in that if I happen to have to add another [CAT] it will
be a lot easier with this function than IIF. 
Thanks Much.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Todd Harpham
Sent: Monday, June 24, 2013 8:03 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Query Problem

Hello -

I guess it's maybe a little late to add to the thread, but I thought I
would submit this anyway since it offers an alternative method of
dealing with these kinds of complex logic problems in query formulation.
Sometimes using the SWITCH statement can simplify matters.  For example:

 

ROUND(SWITCH([CAT]=3,[RCOST], [CAT]=1, [RCOST]/[SumOfBillHours], [CAT]=2
AND [sumOfBillHours]<=40, [RCOST], 

[CAT]=2 AND [sumOfBillHours]>40,
([RCOST]*40+([SumOfBillHours]-40)*[OTCOST])/[SumOfBillHours], [CAT]=4
AND [sumOfBillHours]<=50, [RCOST], [CAT]=4 AND
[sumOfBillHours]>50,([RCOST]*50+([SumOfBillHours]-50)*[OTCOST])/[SumOfBi
llHo
urs]),2) AS AvgCost

 

It looks a little clearer when formatted as you might in VBA:

 

ROUND(SWITCH([CAT]=3,[RCOST], _

                [CAT]=1, [RCOST]/[SumOfBillHours], _

                [CAT]=2 AND [sumOfBillHours]<=40, [RCOST], _

                [CAT]=2 AND [sumOfBillHours]>40,
([RCOST]*40+([SumOfBillHours]-40)*[OTCOST])/[SumOfBillHours], _

                [CAT]=4 AND [sumOfBillHours]<=50, [RCOST], _

                [CAT]=4 AND [sumOfBillHours]>50,
([RCOST]*50+([SumOfBillHours]-50)*[OTCOST])/[SumOfBillHours]),2) AS
AvgCost

 

Hope this helps,

ToddHarpham

 

 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com