434,775 Members | 1,425 Online
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
6 Replies

 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

 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

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

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