IF, Then, Else Statement

[JulieAnnL]

Can anyone tell me why I am "missing an operator" in this expression?

Bonus Amt: If([SumOfQuantity]>75 and <101) Then
=([SumOfQuantity]*5)
ElseIf ([SumOfQuantity])>100 Then
=([SumOfQuantity])*10)
End If

 

[JonoB]

Im not quite sure where you are putting this syntax, but try taking out the &quot;=&quot; signs, and add in a &quot;:&quot;

If([SumOfQuantity]>75 and <101) Then
([SumOfQuantity]*5)
ElseIf[SumOfQuantity])>100 Then
([SumOfQuantity])*10)
End If

Otherwise you may to to reference the actual control
If([SumOfQuantity]>75 and <101) Then
[ControlName] = ([SumOfQuantity]*5)
ElseIf[SumOfQuantity])>100 Then
[ControlName]=([SumOfQuantity])*10)
End If

Hope this helps

 

[Norris68]

You cannot test two values at the same time. You will need to do:

If([SumOfQuantity]>75) and ([SumOfQuantity]<101) Then
AVariable = ([SumOfQuantity]*5)
ElseIf ([SumOfQuantity]>100) Then
AVariable = ([SumOfQuantity]*10)
End If

I have moved the brackets to where they should be (compare to your original post). Also, you will need to assign the result to a variable of some sort (AVariable in this example).
Be aware that Access will not short-circuit the first IF statement - both tests will be calculated, even if the first (>75) is false.

 

[JonoB]

Quite correct, I didnt even see that incorrect syntax for testting two variables at the same time.....

Monday mornings

 

[jebry]

Hi!

This looks like you are working in the query design view. If that is the case then SQL does not recognize If-Then-Else so you need to use a nested IIf like this:

BonusAmt: IIf([SumOfQuantity] > 100, [SumOfQuantity]*10, IIf([SumOfQuantity] > 75, [SumOfQuantity]*5, 0)

This will also work as the control source of a textbox:

=IIf([SumOfQuantity] > 100, [SumOfQuantity]*10, IIf([SumOfQuantity] > 75, [SumOfQuantity]*5, 0)

Of course [SumOfQuantity] needs to be part of the record source of the form or a field in the table or query that the query is based on.

hth


Jeff Bridgham
bridgham@purdue.edu