David McAfee
davidmcafee at gmail.com
Fri Jun 21 12:30:51 CDT 2013
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 >