Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
    
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
add comment

3 Answers

up vote 0 down vote accepted

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

share|improve this answer
add comment

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.

share|improve this answer
    
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
add comment

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?

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.