Access Expression problem: it's too complex, so how do I turn it in to a function?
- by Mike
Access 2007 is telling me that my new expression is to complex. It used to work when we had 10 service levels, but now we have 19! Great! I've asked this question in SuperUser and someone suggested I try it over here. Suggestions are I turn it in to a function - but I'm not sure where to begin and what the function would look like.
My expression is checking the COST of our services in the [PriceCharged] field and then assigning the appropriate HOURS [Servicelevel] when I perform a calculation to work out how much REVENUE each colleague has made when working for a client. The [EstimatedTime] field stores the actual hours each colleague has worked.
[EstimatedTime]/[ServiceLevel]*[PriceCharged]
Below is the breakdown of my COST to HOURS expression. I've put them on different lines to make it easier to read - please do not be put off by the length of this post, it's all the same info in the end.
Many thanks,Mike
ServiceLevel: IIf([pricecharged]=100(COST),6(HOURS),
IIf([pricecharged]=200 Or [pricecharged]=210,12.5,
IIf([pricecharged]=300,19,
IIf([pricecharged]=400 Or [pricecharged]=410,25,
IIf([pricecharged]=500,31,
IIf([pricecharged]=600,37.5,
IIf([pricecharged]=700,43,
IIf([pricecharged]=800 Or [pricecharged]=810,50,
IIf([pricecharged]=900,56,
IIf([pricecharged]=1000,62.5,
IIf([pricecharged]=1100,69,
IIf([pricecharged]=1200 Or [pricecharged]=1210,75,
IIf([pricecharged]=1300 Or [pricecharged]=1310,100,
IIf([pricecharged]=1400,125,
IIf([pricecharged]=1500,150,
IIf([pricecharged]=1600,175,
IIf([pricecharged]=1700,200,
IIf([pricecharged]=1800,225,
IIf([pricecharged]=1900,250,0)))))))))))))))))))
