By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,871 Members | 2,444 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,871 IT Pros & Developers. It's quick & easy.

How to look up for the correct price base on qty

100+
P: 134
I have a table which stored the part nos, qty, price. The Part Nos is repeated cos the qty and price is different.It base on qty volume.

PART NO QTY PRICE
A-0123 1000 1.5
A-0123 7000 1.0

I want to retrieve the correct price base on qty input by user. If user input Part Nos = A-0123, and Qty= 6000, then I should retrieve the price at 1.5. If user enter 10,000 then I should retrieve 1.0.

I have no idea, this is tough for me, Please help
Dec 30 '06 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,489
It would certainly help if the question made more sense... or if the Table name were provided... or you told us where you were trying to use the info.
In the absence of all that I will just have to guess.
I'm assuming you are entering the quantity on a form in the text box txtQty and you want to populate the label lblPrice.
The table used is tblPrice and the product code is displayed on the form in txtProduct.
I'm also assuming that the record layout is as shown ([Part No],[Qty] & [Price]).
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtQty_AfterUpdate()
  2.     Dim curPrice As Currency
  3.     Dim strWhere As String, strResult As String
  4.  
  5.     strResult = "Format([Qty],'0000000000') & [Price]"
  6.     strWhere = "(([Part No]='" & txtProduct & "') AND " & _
  7.                 "([Qty]<=" & txtQty & "))"
  8.     curPrice = CCur(Mid(DMax(strResult, "tblPrice", strWhere), 11))
  9.     lblPrice.Caption = Format(curPrice , "Currency")
  10. End Sub
Dec 31 '06 #2

100+
P: 134
It would certainly help if the question made more sense... or if the Table name were provided... or you told us where you were trying to use the info.
In the absence of all that I will just have to guess.
I'm assuming you are entering the quantity on a form in the text box txtQty and you want to populate the label lblPrice.
The table used is tblPrice and the product code is displayed on the form in txtProduct.
I'm also assuming that the record layout is as shown ([Part No],[Qty] & [Price]).
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtQty_AfterUpdate()
  2.     Dim curPrice As Currency
  3.     Dim strWhere As String, strResult As String
  4.  
  5.     strResult = "Format([Qty],'0000000000') & [Price]"
  6.     strWhere = "(([Part No]='" & txtProduct & "') AND " & _
  7.                 "([Qty]<=" & txtQty & "))"
  8.     curPrice = CCur(Mid(DMax(strResult, "tblPrice", strWhere), 11))
  9.     lblPrice.Caption = Format(, "Currency")
  10. End Sub

Sorry for not giving you more detail as I was too work out on the problem and forget to provide you more detail, But you have guess 100% correctly, and thank for that hard to find coding, and exactly what I want, I have tried it out, it work.

Thank You So Much and Happy New Year to You.
Dec 31 '06 #3

NeoPa
Expert Mod 15k+
P: 31,489
You're welcome.

Happy New Year :)
Dec 31 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.