If Then Else Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do you incorporate a range of numbers with an if..then..else statement? Here's how my code starts

Private Sub ProductID_AfterUpdate(
If Me!ProductID = 12 and Me!Quantity >1The
Me!QTYDiscount =
Els
Me!QTYDiscount =
End I

Simple enough. But now, I want to say that if ProductID=14 and quantity is between 10 and 39 then the quantity discount (QTYDiscount) should be .5
If the quantity is greater than 39 for the same product ID then the quantity discount should be 1.

How do you say that in VBA, which doesn't like Between And. Appreciate any help! Thank yo
Ken
 
deja vu (sp?)! are you KAT, who posted the same question yesterday? even if
not, here's my answer from that thread, which may help you:

With Me
If .ProductID = 266 Then
Select Case Nz(.Quantity,0)
Case 10 To 39
.QuantityDiscount = .5
Case Is > 39
.QuantityDiscount = 1
Case Else
.QuantityDiscount = 0
End Select
Else If .ProductID = 380 Then
'etc, etc, etc.
End With

But, if your company offers varying quantity discounts based on the product
selected as well as the quantity, it might be better not to "hard-code" the
product ids and quantities. instead, you might want to build a table to use
as a matrix, and then pull the discount from the table as needed.

example:
tblDiscounts
ProductID MinimumNumber DiscountPercent
200 00 00
200 10 10
200 50 20
(any amount over 50 gets 20%, no higher discount available for this product)
201 00 00
201 25 15
201 75 30
201 150 50
(any amount over 150 gets 50%, no higher discount available for this
product)
etc,etc,etc.

you can use a DLookup() function or a Recordset search, in code, to
determine the applicable discount at runtime. this setup has the advantage
of allowing you to be completely flexible in assigning discount amounts,
without having to change your VBA code every time a discount changes or a
new product is added.

hth


KEN said:
How do you incorporate a range of numbers with an if..then..else
Click to expand...
statement? Here's how my code starts:
Private Sub ProductID_AfterUpdate()
If Me!ProductID = 12 and Me!Quantity >1Then
Me!QTYDiscount = 1
Else
Me!QTYDiscount = 0
End If

Simple enough. But now, I want to say that if ProductID=14 and quantity is
Click to expand...
between 10 and 39 then the quantity discount (QTYDiscount) should be .50
 
Ken,

You could do it this way.

If Me.ProductID = 14 Then
If Me.Quantity >39 Then
Me.QTYDiscount = 1
Else If Me.Qty >9 Then
Me.QTYDiscount = 0.5
Else
Me.QTYDiscount = 0.0
End If
End If


By using the nested statement the system will only have to
go through the original statement if the product is not #14
this is immaterial if you have only 14 items with this
kind of code, but if you have several hundred, it could
mean the difference in a few seconds worth of delay.

We start with the high end quantity. Then if the quantity
is over 39 it will put in the discount and go to the end
of the statement. If the qty is less than 40 it will then
go to the Else If line and if the qty is over 9 it will
input that discount and skip to the end of the if
statement. If the qty is less than 10 it will go to the
Else sttement and set the discount to 0.

HTH


AJ
-----Original Message-----
How do you incorporate a range of numbers with an
Click to expand...
if..then..else statement? Here's how my code starts:
Private Sub ProductID_AfterUpdate()
If Me!ProductID = 12 and Me!Quantity >1Then
Me!QTYDiscount = 1
Else
Me!QTYDiscount = 0
End If

Simple enough. But now, I want to say that if
Click to expand...
ProductID=14 and quantity is between 10 and 39 then the
quantity discount (QTYDiscount) should be .50
If the quantity is greater than 39 for the same product
Click to expand...
ID then the quantity discount should be 1.
How do you say that in VBA, which doesn't like Between
Click to expand...
And. Appreciate any help! Thank you
 
I think this is what you want:
Private Sub ProductID_AfterUpdate()
If Me!ProductID = 12 and Me!Quantity >1 Then
Click to expand...
Me!QTYDiscount = 1
Else if Me!ProductID = 14 and Me!Quantity>10 and Me!
Quantity<39 then
Me!QTYDiscount=0.5
Else
Me!QTYDiscount = 0
End If

-----Original Message-----
How do you incorporate a range of numbers with an
Click to expand...
if..then..else statement? Here's how my code starts:
Private Sub ProductID_AfterUpdate()
If Me!ProductID = 12 and Me!Quantity >1Then
Me!QTYDiscount = 1
Else
Me!QTYDiscount = 0
End If

Simple enough. But now, I want to say that if
Click to expand...
ProductID=14 and quantity is between 10 and 39 then the
quantity discount (QTYDiscount) should be .50
If the quantity is greater than 39 for the same product
Click to expand...
ID then the quantity discount should be 1.
How do you say that in VBA, which doesn't like Between
Click to expand...
And. Appreciate any help! Thank you
 
See the thread below entitled
RANGE of Values in IF Clause - HOW?? Calling all braniacs.

It covers the same topic.
Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
How do you incorporate a range of numbers with an
Click to expand...
if..then..else statement? Here's how my code starts:
Private Sub ProductID_AfterUpdate()
If Me!ProductID = 12 and Me!Quantity >1Then
Me!QTYDiscount = 1
Else
Me!QTYDiscount = 0
End If

Simple enough. But now, I want to say that if ProductID=14
Click to expand...
and quantity is between 10 and 39 then the quantity
discount (QTYDiscount) should be .50
If the quantity is greater than 39 for the same product ID
Click to expand...
then the quantity discount should be 1.
How do you say that in VBA, which doesn't like Between
Click to expand...
And. Appreciate any help! Thank you
 
Back
Top