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

RocketGoal

Posted 2010-04-15T10:42:02.767

Reputation: 1 468

As much as Access SQL isn't "real programming", you probably should have posted this on Stack Overflow. – ta.speot.is – 2010-04-15T11:07:37.083

Probably right, but I was thinking along the same lines of Access being less about 'programming' more about office Software – RocketGoal – 2010-04-15T12:18:30.827

Answers

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

David W. Fenton

Posted 2010-04-15T10:42:02.767

Reputation: 984

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.

Also upgrade to SQL Server.

ta.speot.is

Posted 2010-04-15T10:42:02.767

Reputation: 13 727

Editing my answer is giving me an error page... you may also want to try... (forgive the formatting): – ta.speot.is – 2010-04-15T11:14:25.137

#1: IIF(Value BETWEEN 0 AND 1000, (put IIFs for 0 to 1000), (put IIFs for 1001 to 2000)) – ta.speot.is – 2010-04-15T11:14:48.037

#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 – 2010-04-15T11:16:39.203

I like the idea of a function GetRevenue - but I have little knowledge in writing them. – RocketGoal – 2010-04-15T12:15:55.027

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?

THEn

Posted 2010-04-15T10:42:02.767

Reputation: 242

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 – 2010-04-16T07:39:09.057