[AccessD] Query Problem

I've had to do that before too.

It does make it easier to read/debug

I don't think there is much of a performance difference because both
conditions are tested in an IIF (IIRC)

Glad you got it working

David


On Fri, Jun 21, 2013 at 6:35 AM, Steve Turner <sturner at mseco.com> wrote:

> Thanks Paul,
>  I thought that would be the problem but it wouldn't accept it. David
> made a suggestion to break it down so I did the True/False diagram and
> got one to accept but when I ran the query it wouldn't process correctly
> so I took another route and changed the field name on the original added
> another field for the second condition and combined the two into the
> AvgCost field. I got that to work so hopefully that will solve the
> problem.
> Steve
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Paul Hartland
> Sent: Friday, June 21, 2013 2:21 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Query Problem
>
> If the first line works and you get a problem when adding the 2nd line
> there is a missing ) from the end of the 2nd line,
>
> Second Line:
> IIf([CAT]=4,IIf([SumOfBillHours]<=50,[RCOST],(([RCOST]*50)+([SumOfBillHo
> urs]-50)*[OTCOST])/[SumOfBillHours])
>
> Should be
>
> Second Line:
> IIf([CAT]=4,IIf([SumOfBillHours]<=50,[RCOST],(([RCOST]*50)+([SumOfBillHo
> urs]-50)*[OTCOST])/[SumOfBillHours]))
>
> Then try adding it to where you want it in the first line
>
> Paul
>
>
> On 20 June 2013 17:54, 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
> >
>
>
>
> --
> Paul Hartland
> paul.hartland at googlemail.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
>