434,933 Members | 1,223 Online
Need help? Post your question and get tips & solutions from a community of 434,933 IT Pros & Developers. It's quick & easy.

# Syntax Error in an IF statement

 P: 9 Help! I am trying to do an If statement but keep getting a "syntax error (comma)" when I try to run it. Expand|Select|Wrap|Line Numbers  Expr1: If([Sales_Amount]<21000000,"5 – 20M",If([Sales_Amount] Between 20999999 And 51000000,"21 – 50M",If([Sales_Amount] Between 50999999 And 101000000,"51 – 100M"," ")))  I am using Access 2000. In case it isn't clear what I'm trying to do I would like it to return a value - If the sales amount is less than 210000000 return the value of "5 - 20M", if not less than 21000000 then is sales amount between 20999999 and 5100000 then return the value "21 - 50M", and if not then is the value of the sales amount between 50999999 And 101000000 then return a value of "51 – 100M" else leave it blank. Any assistance is greatly appreciated. Feb 15 '07 #1
7 Replies

 P: 4 I think you want iif not if.. Help! I am trying to do an If statement but keep getting a "syntax error (comma)" when I try to run it. Expand|Select|Wrap|Line Numbers  Expr1: If([Sales_Amount]<21000000,"5 – 20M",If([Sales_Amount] Between 20999999 And 51000000,"21 – 50M",If([Sales_Amount] Between 50999999 And 101000000,"51 – 100M"," ")))  I am using Access 2000. In case it isn't clear what I'm trying to do I would like it to return a value - If the sales amount is less than 210000000 return the value of "5 - 20M", if not less than 21000000 then is sales amount between 20999999 and 5100000 then return the value "21 - 50M", and if not then is the value of the sales amount between 50999999 And 101000000 then return a value of "51 – 100M" else leave it blank. Any assistance is greatly appreciated. Feb 15 '07 #2

 Expert 5K+ P: 8,638 Help! I am trying to do an If statement but keep getting a "syntax error (comma)" when I try to run it. Expand|Select|Wrap|Line Numbers  Expr1: If([Sales_Amount]<21000000,"5 – 20M",If([Sales_Amount] Between 20999999 And 51000000,"21 – 50M",If([Sales_Amount] Between 50999999 And 101000000,"51 – 100M"," ")))  I am using Access 2000. In case it isn't clear what I'm trying to do I would like it to return a value - If the sales amount is less than 210000000 return the value of "5 - 20M", if not less than 21000000 then is sales amount between 20999999 and 5100000 then return the value "21 - 50M", and if not then is the value of the sales amount between 50999999 And 101000000 then return a value of "51 – 100M" else leave it blank. Any assistance is greatly appreciated. I feel as though this would be an easier solution: Expand|Select|Wrap|Line Numbers Sales Range: fCalculateSalesRange([Sales]) Where fCalculateSalesRange() is: Expand|Select|Wrap|Line Numbers Public Function fCalculateSalesRange(MySales) As String    If MySales >= 50999999 And MySales <= 101000000 Then       fCalculateSalesAmount = "51 - 100M"    ElseIf MySales >= 20999999 And MySales <= 51000000 Then       fCalculateSalesAmount = "21 - 50M"    ElseIf MySales < 21000000 Then       fCalculateSalesAmount = "5 - 20M"    Else       fCalculateSalesAmount = vbNullString    End If End Function Sample OUTPUT: Expand|Select|Wrap|Line Numbers Sales                Sales Range \$20,513,456.00        5 - 20M \$30,000,000.00       21 - 50M \$75,000,000.00       51 - 100M \$21,000,000.00       21 - 50M \$51,000,000.00       51 - 100M \$1,230,000,000.00     Any other questions, feel free to ask. Feb 15 '07 #3

 P: 9 Reply to both of you in one. Have never heard of IIF statement - I will look at that. As to the other code that looks great, but where do I put the code. usually I would type the expression in the design view window or in the sql view. not sure how to put the VB script into the query. How do I do that? Thanks both of you for your help! Feb 15 '07 #4

 P: 9 Thanks Brisokol! Although the iif did not work for me it did get me to learn about switch function which did work for me! So thanks! Feb 15 '07 #5

 P: 9 Thanks ADezii! I did not get to use your code because while researching the iif function I learned about the switch function which did the trick! I would like to learn how to do it your way. However as I mentioned above, where do I put the code. usually I would type the expression in the design view window or in the sql view. not sure how to put the VB script into the query. How do I do that? Thanks you for your help! Jim Feb 15 '07 #6

 Expert Mod 15k+ P: 31,492 Thanks ADezii! I did not get to use your code because while researching the iif function I learned about the switch function which did the trick! I would like to learn how to do it your way. However as I mentioned above, where do I put the code. usually I would type the expression in the design view window or in the sql view. not sure how to put the VB script into the query. How do I do that? Thanks you for your help! Jim Nice to reply to all assistance :) To use code (VBA) you need to create a module (Alt-F11 from Access; Insert / Module). Type the code in here then compile and close. This function will then be available to all code (including within SQL) in your project. HTH. Feb 15 '07 #7

 Expert Mod 15k+ P: 31,492 IIf() is a very important function in SQL. Switch() is very useful (especially in this case) but one of my favourites is Choose(). Choose(index, choice-1[, choice-2, ... [, choice-n]]) The Choose function syntax has these parts: Part Description index Required. Numeric expression or field that results in a value between 1 and the number of available choices. choice Required. Variant expression containing one of the possible choices. Feb 15 '07 #8