By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,455 Members | 2,154 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,455 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
  1.  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
Share this Question
Share on Google+
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
  1.  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

ADezii
Expert 5K+
P: 8,597
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
  1.  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
  1. Sales Range: fCalculateSalesRange([Sales])
Where fCalculateSalesRange() is:
Expand|Select|Wrap|Line Numbers
  1. Public Function fCalculateSalesRange(MySales) As String
  2.    If MySales >= 50999999 And MySales <= 101000000 Then
  3.       fCalculateSalesAmount = "51 - 100M"
  4.    ElseIf MySales >= 20999999 And MySales <= 51000000 Then
  5.       fCalculateSalesAmount = "21 - 50M"
  6.    ElseIf MySales < 21000000 Then
  7.       fCalculateSalesAmount = "5 - 20M"
  8.    Else
  9.       fCalculateSalesAmount = vbNullString
  10.    End If
  11. End Function
Sample OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Sales                Sales Range
  2. $20,513,456.00        5 - 20M
  3. $30,000,000.00       21 - 50M
  4. $75,000,000.00       51 - 100M
  5. $21,000,000.00       21 - 50M
  6. $51,000,000.00       51 - 100M
  7. $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

NeoPa
Expert Mod 15k+
P: 31,186
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

NeoPa
Expert Mod 15k+
P: 31,186
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

Post your reply

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