Syntax Error in an IF statement

[erajim]

Help!

I am trying to do an If statement but keep getting a "syntax error (comma)" when I try to run it.

 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.

[Brisokol]

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.

 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.

[ADezii]

Help!

I am trying to do an If statement but keep getting a "syntax error (comma)" when I try to run it.

 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:

Sales Range: fCalculateSalesRange([Sales])

Where fCalculateSalesRange() is:

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:

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.

[erajim]

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!

[erajim]

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!

[erajim]

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

[NeoPa]

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.

[NeoPa]

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.