[AccessD] Query Problem

You're right, I like to put these in Note pad (or SSMS) and indent, as if
it we HTML:

First Line:
AvgCost:
Round(
 IIf([CAT]=3,[RCOST],
  IIf([CAT]=1, [RCOST]/[SumOfBillHours],
   IIf([CAT]=2,
    IIf([SumOfBillHours]<=40, [RCOST],
     (--open of 4th IIF False's divisor
      ([RCOST]*40) + ([SumOfBillHours]-40) * [OTCOST]
     )--close of 4th IFF False divisor
     /[SumOfBillHours]
    )--4th IIF (True condition of 3rd IIF)
   )--3rd IIF (False condition of 2nd IIF)
  )--2nd IIF (False Condition of 1st IIF)
 )--1st IIF
,2)--Round

Would it work if you put an extra set of parenthesis around the false
division problem?

Like this?

First Line:
AvgCost:
Round(
 IIf([CAT]=3,[RCOST],
  IIf([CAT]=1, [RCOST]/[SumOfBillHours],
   IIf([CAT]=2,
    IIf([SumOfBillHours]<=40, [RCOST],
(--Add this
     (--open of 4th IIF False's divisor
      ([RCOST]*40) + ([SumOfBillHours]-40) * [OTCOST]
     )--close of 4th IFF False divisor
     /[SumOfBillHours]
) --and this
    )--4th IIF (True condition of 3rd IIF)
   )--3rd IIF (False condition of 2nd IIF)
  )--2nd IIF (False Condition of 1st IIF)
 )--1st IIF
,2)--Round

if not, try simplifying it like this:

First Line:
AvgCost:
Round(
 IIf([CAT]=3,[RCOST],
  IIf([CAT]=1, [RCOST]/[SumOfBillHours],
   IIf([CAT]=2,
    IIf([SumOfBillHours]<=40, [RCOST],
     -10000
    )--4th IIF (True condition of 3rd IIF)
   )--3rd IIF (False condition of 2nd IIF)
  )--2nd IIF (False Condition of 1st IIF)
 )--1st IIF
,2)--Round

just as a test...


On Thu, Jun 20, 2013 at 10:21 AM, Steve Turner <sturner at mseco.com> wrote:

> David, That's what I thought too but I added One says noway added
> another same thing. Tried quite a few and still no luck. That's what's
> puzzling me. May be I can't have that many nested "iif's" But I thought
> it should work.
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
> Sent: Thursday, June 20, 2013 12:12 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Query Problem
>
> I believe you are missing a close parenthesis for the first IIF.
>
> It should be the added as the 4th character from the end:
> <here> ,2)
> that would give you five closing parethesis at the end:
>
> [SumOfBillHours]))))),2)
>
>
>
>
> On Thu, Jun 20, 2013 at 9:54 AM, Steve Turner <sturner at mseco.com> wrote:
>
> >
> >
> > Guys, I have a query that uses this expression AvgCost:  I'm trying to
>
> > add the second line iif([CAT] = 4 to the end of the first line after
> > [SumOfBillHours] as an additional condition but the number of
> > parentheses is giving me fits when
> >
> > I try to save it. Keeps telling me I have the wrong number of them and
>
> > I add and add but to no avail. Anyone see why it won't work. Missing a
>
> > parentheses somewhere I guess. The first line works but I needed to
> > add one more condition.
> >
> >
> >
> > First Line:    AvgCost:
> > Round(IIf([CAT]=3,[RCOST],IIf([CAT]=1,[RCOST]/[SumOfBillHours],IIf([CA
> > T]
> > =2,IIf([SumOfBillHours]<=40,[RCOST],(([RCOST]*40)+([SumOfBillHours]-40
> > )*
> > [OTCOST])/[SumOfBillHours])))),2)
> >
> >
> >
> > Second Line:
> > IIf([CAT]=4,IIf([SumOfBillHours]<=50,[RCOST],(([RCOST]*50)+([SumOfBill
> > Ho
> > urs]-50)*[OTCOST])/[SumOfBillHours])
> >
> >
> >
> >
> > Steve A.Turner
> >
> > Controller
> >
> > Mid-South Engineering Co. Inc.
> >
> > P.O. Box 1399
> >
> > 1658 Malvern Ave.
> >
> > Hot Springs, AR 71902
> >
> > Phone: 501-321-2276
> >
> > Fax 501-321-4750
> >
> > Cell 501-282-7751
> >
> > Email sturner at mseco.com
> >
> >
> >
> > --
> > 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>