[AccessD] Query Problem

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