By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,775 Members | 1,425 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,775 IT Pros & Developers. It's quick & easy.

Calculation based on If...Then Statement

P: 4
I have a question about how to perform a calculation. I'm using Access 2003 in Windows XP.

I have three fields of different measurements, which I'll call A, B, and C. Those numbers get crunched to yield X, Y, and Z, respectively, in a query. No problem so far. What I want to do next is calculate N, but how I do that depends on the value of A/B:

If A/B >30
Then N=(Y+Z)/2

If A/B <10
Then N=(X+Z)/2

If 10<= A/B <=30
Then N=( (X+Y)/2 + Z)/2

Can I make this happen in a query?

I've tried various ways of entering If...Then...ElseIf..., with different combinations of parentheses and brackets to separate terms, but I keep getting an error that says "The expression you entered contains invalid syntax. You may have entered an operand without an operator." After I click OK, my first Then in the statement is highlighted.

I would appreciate any help in getting this calculation to work. I'm still new at Access, so please give step-by-step instructions if you can. Many thanks!
Mar 20 '07 #1
Share this Question
Share on Google+
6 Replies


Rabbit
Expert Mod 10K+
P: 12,366
What's the code you're using? You can't just use an if/then in a query. You have to use iif(expression, value if true, value if false).
Mar 20 '07 #2

P: 4
What's the code you're using?
This is my latest attempt in the query's design view:
TSI: If ([TNdTP]>30) Then (([TSITP]+[TSISD])/2)
[Else (([TSITN]+[TSISD])/2)]

When I change that If to iif, it gives me this error: The expression you entered has a function containing the wrong number of arguments.
Mar 20 '07 #3

Rabbit
Expert Mod 10K+
P: 12,366
It looks like you replied before I made my edits. You'll need to use the iif() function. And you can nest them within each other as a quick fix.
Mar 20 '07 #4

Rabbit
Expert Mod 10K+
P: 12,366
This is my latest attempt in the query's design view:
TSI: If ([TNdTP]>30) Then (([TSITP]+[TSISD])/2)
[Else (([TSITN]+[TSISD])/2)]

When I change that If to iif, it gives me this error: The expression you entered has a function containing the wrong number of arguments.
Well, you can't merely change If to iif because the syntax would be wrong.
Expand|Select|Wrap|Line Numbers
  1. TSI: iif ([TNdTP]>30, [TSITP]+[TSISD])/2, [TSITN]+[TSISD])/2)
Mar 20 '07 #5

P: 4
Using nested iif statements makes it work. Thanks so much!
Mar 20 '07 #6

Rabbit
Expert Mod 10K+
P: 12,366
Not a problem, good luck.
Mar 20 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.