Trying to import or recreate a multiplier formula from excel to access

[Vicar]

Simultaneous multi-discount calculation

Really need help on this one, here is an excel expression:

X Multipliers
$ Value Multiplier

is less than 5 then *3.8
is less than 50 *3.2
is less than 100 *2.8
is less than 200 *2.4
is less than 500 *2
is greater than 500 *1.8

so if price is less then 5 then multiply by 3.8 and so on. In excel it's in a separate worksheet, and other worksheets linked to it and adjust their Sale Price from the Cost column. Someone else created this spreadsheet, and i couldn't find a multiple discount help in access. I tried creating a query
=([PriceBook_OLD].[Cost]<=5)*3.8 Or ([PriceBook_OLD].[Cost]<=50)*3.2 Or ([PriceBook_OLD].[Cost]<=100)*2.8 Or ([PriceBook_OLD].[Cost]<=200)*2.4 Or ([PriceBook_OLD].[Cost]<=500)*2 Or ([PriceBook_OLD].[Cost]>500)*1.8
but it doesn't work. Please help!!!!
Thank you very much

V.

 

[M8KWR]

are you using this on form to autmatically update a field, if so then a select case would be the best procedure to use!!

If you are unsure how to write a select case then let me know.

 

[Vicar]

Yes, on the form will be just fine, with all those conditions if possible.
Thank you very much!

 

[M8KWR]

I hope this helps, and i have done all the symbols correct, it not let me know.

I would put this in the after_update procedure of the fields that you need to query, which is this example would be Value1

Any issue let me know.

Dim Value1, Value2


Value1 = [value that you are querying]

Select Case Value1

Case <=5

Value2 = Vaue1 * 3.8

Case >5 and <=50

Value2 = Vaue1 * 3.2

Case >50 and <=100

Value2 = Vaue1 * 2.8

Case >100 and <=200

Value2 = Vaue1 * 2.4

Case >200 and <=500

Value2 = Vaue1 * 2

Case <500

Value2 = Vaue1 * 1.8

Case

end select

Value2 = [what every field you need this to be]

 

[Smart]

RE : Simultaneous multi-discount calculation

You could use a query;

Place the following code in your query

New: IIf(([PriceBook_OLD].[Cost]<=5),[PriceBook_OLD].[Cost]*3.8,IIf(([PriceBook_OLD].[Cost]<=50),[PriceBook_OLD].[Cost]*3.2,IIf(([PriceBook_OLD].[Cost]<=100),[PriceBook_OLD].[Cost]*2.8,IIf(([PriceBook_OLD].[Cost]<=200),[PriceBook_OLD].[Cost]*2.4,IIf(([PriceBook_OLD].[Cost]<=500),[PriceBook_OLD].[Cost]*2,IIf(([PriceBook_OLD].[Cost]>500),[PriceBook_OLD].[Cost]*1.8))))))

 

[Vicar]

Dim Value1, Value2


Value1 = [PriceBook_OLD.Cost]

Select Case Value1

Case Is <= 5

Value2 = Vaue1 * 3.8

Case >5 and <=50

Value2 = Vaue1 * 3.2

Case >50 and <=100

Value2 = Vaue1 * 2.8

Case >100 and <=200

Value2 = Vaue1 * 2.4

Case >200 and <=500

Value2 = Vaue1 * 2

Case Is < 500

Value2 = Vaue1 * 1.8

Case

End Select

Value2 = [PriceBook_OLD.Price]
End Sub


i don't know why they are listed red in VB, but the values in form don't change. and no error codes

 

[M8KWR]

Sorry completely my fault, as i was tryin to do this for you and do something else...

the correct code is

Dim Value1, Value2


Value1 = [PriceBook_OLD.Cost]

Select Case Value1

Case Is <= 5

Value2 = Value1 * 3.8

Case Is > 5 And Value1 <= 50

Value2 = Value1 * 3.2

Case Is > 50 And Value1 <= 100

Value2 = Value1 * 2.8

Case Is > 100 And Value1 <= 200

Value2 = Value1 * 2.4

Case Is > 200 And Value1 <= 500

Value2 = Value1 * 2

Case Is < 500

Value2 = Value1 * 1.8


End Select

[PriceBook_OLD.Price] = Value2

Sorry for any confussion... I hope this works now..

 

[Vicar]

Unfortunately this formula IIf(([PriceBook_OLD].[Cost]<=5),[PriceBook_OLD].[Cost]*3.8,IIf(([PriceBook_OLD].[Cost]<=50),[PriceBook_OLD].[Cost]*3.2,IIf(([PriceBook_OLD].[Cost]<=100),[PriceBook_OLD].[Cost]*2.8,IIf(([PriceBook_OLD].[Cost]<=200),[PriceBook_OLD].[Cost]*2.4,IIf(([PriceBook_OLD].[Cost]<=500),[PriceBook_OLD].[Cost]*2,IIf(([PriceBook_OLD].[Cost]>500),[PriceBook_OLD].[Cost]*1.8))))))
doesn't work eaither. out of 835 records it shows me about a 100 and all of them 0's . I did check the table hoping that it just showed me zero's since it cannot convert them, but no. I also broke it down in pieces, which i realize won't make a difference.
No errors, so i can't see what's the problem. But thank you for your help.

 

[RuralGuy]

Dim Value1, Value2
Value1 = [PriceBook_OLD.Cost]

Select Case Value1
   Case Is <= 5
      Value2 = Value1 * 3.8
   Case Is <= 50
      Value2 = Value1 * 3.2
   Case Is <= 100
      Value2 = Value1 * 2.8
   Case Is <= 200
      Value2 = Value1 * 2.4
   Case Is <= 500
      Value2 = Value1 * 2
   Case Else
      Value2 = Value1 * 1.8
End Select
[PriceBook_OLD.Price] = Value2

I just wanted to point out that once a Case statement is matched, the code directly below it executes and then the flow is out of the Select structure. No further Case examination takes place. Therefore the lower limit is not necessary since it would have found a slot to exit already.

 

[Vicar]

Sorry completely my fault, as i was tryin to do this for you and do something else...

the correct code is

Dim Value1, Value2


Value1 = [PriceBook_OLD.Cost]

Select Case Value1

Case Is <= 5

Value2 = Value1 * 3.8

Case Is > 5 And Value1 <= 50

Value2 = Value1 * 3.2

Case Is > 50 And Value1 <= 100

Value2 = Value1 * 2.8

Case Is > 100 And Value1 <= 200

Value2 = Value1 * 2.4

Case Is > 200 And Value1 <= 500

Value2 = Value1 * 2

Case Is < 500

Value2 = Value1 * 1.8


End Select

[PriceBook_OLD.Price] = Value2

Sorry for any confussion... I hope this works now..

it looks good but same thing, no change even though nothing is restricted.
Thank you

 

[Vicar]

Database sample

here it is, maybe it will be easier if you can see the actual thing
Thank you all very much!

 

[RuralGuy]

You will have strange results if the original value is > 500.

 

[The_Doc_Man]

In the module screen, either edit an existing module or (if you don't have any) create a new one. To other readers: If you look at the code CASE generates, it is nearly identical to this anyway, so it merely LOOKS inefficient. But it isn't. (Doubt me? Single-step through your CASE statements. You'll touch each CASE option anyway.)

Public Function DiscountRate( X as Single ) as Single

Dim Y as Single

Y = 3.8
If X > 5 then Y = 3.2
If X > 50 then Y = 2.8
if X > 100 then Y = 2.4
if X > 200 then Y = 2
if X > 500 then Y = 1.8
DiscountRate = Y

End Function

Now compile and save this module. You can then use the DiscountRate function ANYWHERE on a form, report, or query.

SalesPrice = Quantity * UnitPrice * DiscountRate( Quantity )

Is X only a LONG (integer)....? Then instead of X as Single, use X as Integer in the function declaration. Just be consistent, and note that the above would faile unless you used

SalesPrice = CSngl( Quantity ) * UnitPrice * DiscountRate( Quantity )

 

[Vicar]

It WORKS

Thank you all!!!!! It works, my own fault!
I was attaching event procedure to the price, when it should have gone to the cost.
Dumas

 

[RuralGuy]

You got me curious Doc so I single stepped through the Select Case. It works as I have described.

 

[The_Doc_Man]

RG - you have to eval all of the CASE options until you hit the right one. You hit them one at a time. For the short list Vicar had, the difference will be microseconds. A CASE is implied to work as

IF case1 THEN do something
ELSEIF case2 THEN do something else
ELSEIF case3 THEN try again....
ELSEIF case4....

etc.

Until you hit the right one you evaluate every ELSEIF condition.

 

[RuralGuy]

But unlike your algorithm (which there is nothing wrong with by the way) the Select Case exits when there is a hit. No more If testing. My point in posting was I do not believe you have single stepped the Case statement or you would have seen that it does *not* touch each Case option as you described.

It is just a fine point and not worth arguing about. I enjoy your solutions to problems. Keep it up.