Calculate correct cost from sliding scale of prices

[addy]

Guys, need some assistance if possible.

I have a sliding scale of charges for particlar items, so depending on how many are ordered te price per unit may vary. Example:

Item Number | Cost 1+ | Cost 1000+ | Cost 5000+
1 4.00 2.00 0.75
2 3.00 1.50 0.75
3 2.00 1.25 0.75

What I am looking at doing is having a form which is part of a larger application where a user can select, for a particular job, an item from the above example and enter the number ordered, e.g. For item 1, 2356 ordered. I then need the database to know that the correct price to use for calculating the total of this will be 2.00 and not 4.00 or 0.75

I guess what I need is a replacement for the vlookup function in Excel. How should I best design my table and how do i get my form to know which value to use?

Many Thanks.

 

[Remou]

This would be easier if your table was like this:

[tt]Item Number | LowerRange| UpperRange | Cost
1 1 999 4.00
1 1000 4999 2.00
1 5000 9999 0.75[/tt]

strSQL="Select from tblCosts Where ItemNo=" & intItemNo _
& " And " & intCost & " Between LowerRange And UpperRange"

 

[addy]

Remou

Thanks forthe very quick reply.

This looks like it may simplify things for me and looks a nice solution.

Will post back with how it goes - be prepared for a star!