Access 2007: How can I make this EXPRESSION less complex?

Access 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!

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]
``````

Great. 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)))))))))))))))))))

-
As much as Access SQL isn't "real programming", you probably should have posted this on Stack Overflow. – ta.speot.is Apr 15 '10 at 11:07
Probably right, but I was thinking along the same lines of Access being less about 'programming' more about office Software – RocketGoal Apr 15 '10 at 12:18

You should be storing the values used in your expression in a table. That way you can do the calculation with a SQL join and a few of IIf()'s. And you'll avoid complex expressions (which are nearly impossible to debug) and dependency on VBA (which will make your query inaccessible outside of Access itself).

-

Are you using JET or Access? If you are using Access create a VBA function that uses `Select Case` and replace that mess of code with `GetServiceLevel([PriceCharged]) AS ServiceLevel`. Better yet, write a function called `GetRevenue`.

-
Editing my answer is giving me an error page... you may also want to try... (forgive the formatting): – ta.speot.is Apr 15 '10 at 11:14
#1: IIF(Value BETWEEN 0 AND 1000, (put IIFs for 0 to 1000), (put IIFs for 1001 to 2000)) – ta.speot.is Apr 15 '10 at 11:14
#2: SELECT IIf(Value BETWEEN 0 AND 1000, [A].ServiceLevel, [B].ServiceLevel) FROM ( SELECT IIF( (put IIFs for 0 to 1000) AS ServiceLevel ) ) AS [A], ( SELECT IIF( (put IIFs for 1001 to 2000) AS ServiceLevel ) ) AS [B] – ta.speot.is Apr 15 '10 at 11:16
I like the idea of a function GetRevenue - but I have little knowledge in writing them. – RocketGoal Apr 15 '10 at 12:15

Where is this expression used in form or report or query?

I would think the best way would be creating a global function and use the function.

EDIT: btw have tried the Switch function yet?

-
The expression sits inside the SELECT query, where all my calculations and filtering are done. Works a charm (well did). I'm under the impression it'll be better to make a table, as commented above, and then try and make it join. However, as this database is not mine to amend I can only work with limited possibilities. – RocketGoal Apr 16 '10 at 7:39