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

How to Search the Range Value

100+
P: 134
I have a table called tblPrice consist of ProductID, SupplierID, Qty and Price,
Which I use to store the pricing.

Different Supplier may quote a different price on a same product base on Qty Purchase.

At the end of the day, so I can see which Supplier quoted the lowest price, so I can purchase from that supplier.

The format of the table as below:-
[TBLPRICE]

PRODUCTID | SUPPLIERID | PUR QTY | PRICE

BT-0007 SP0001 1,000 $1.50 (from 1k onward)
BT-0007 SP0001 5,000 $1.30
BT-0007 SP0001 12,000 $1.20 (from 12k onward)

BT-0007 SP-0007 1,000 $1.56
BT-0007 SP-0007 5,000 $1.35
BT-0007 SP-0007 12,000 $1.25

In the Form, I have 2 textbox - txtProdID, txtQty, If I enter BT-0007 for ProdID and 7000 for Qty
it will show the result like this : -, regardless whether in report or form.


BT-0007 SP0001 $1.30
BT-0007 SP-0007 $1.35

So I can see & compare the lowst price.
I have tried using query, it didn;t work, Any Solution ?
Jan 1 '07 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,661
Can you explain how this is different from the question in (How to look up for the correct price base on qty)?
Maybe I'm missing something obvious (other than there is more info posted here :) of course).
Jan 1 '07 #2

100+
P: 134
Can you explain how this is different from the question in (How to look up for the correct price base on qty)?
Maybe I'm missing something obvious (other than there is more info posted here :) of course).
Thanks, It sound similar to the previous question, which able to search for the right currency base on the qty. This question was to display the entire row, this mean to display along with the supplier name, price, the table could repeat the same product with many different supplier quoting the same qty, but the price maybe different, therefore I need to search for the lowest price.
Jan 1 '07 #3

NeoPa
Expert Mod 15k+
P: 31,661
I'm getting so frustrated to be wasting so much time on simple communication.
I'm sorry, but the only sense I could get from all that was that you want a whole record rather than a single value.
Why do you miss out words and letters and such like from an explanation? Do you want to make it hard to understand? Can you understand it if you reread it?
Please ignore this tirade if you are not a native English speaker - Then it would be understandable and a restriction I'm happy to work within.
Otherwise, I will simply ignore a post in future if it is not clear on the first couple of readings.

I went back to the first post and re-read it in detail and I think I've got what you're asking now.
You need a ComboBox to show the results and the RowSource of the ComboBox (We will call it cboResults) should be set like :
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtProdID_AfterUpdate()
  2.     Call ComboSource
  3. End Sub
  4.  
  5. Private Sub txtQty_AfterUpdate()
  6.     Call ComboSource
  7. End Sub
  8.  
  9. Private Sub ComboSource()
  10.     Dim strSQL As String, strPrice As String
  11.  
  12.     If Nz(txtProdID, "") = "" Or Nz(txtQty, 0) = 0 Then
  13.         strSQL = ""
  14.     Else
  15.         strPrice = "CCur(Mid(Max(" & _
  16.                    "Format([Pur Qty],'0000000000') & " & _
  17.                    "[Price]),11)) "
  18.         strSQL = "SELECT ProductID," & _
  19.                         "SupplierID," & _
  20.                         strPrice & _
  21.                      "FROM tblPrice " & _
  22.                      "WHERE ((ProductID='" & txtProdID & "')" & _
  23.                      "  AND ([Pur Qty]<=" & txtQty & ")) " & _
  24.                      "GROUP BY SupplierID"
  25.     End If
  26.     cboResults.RowSource = strSQL
  27. End Sub
Jan 1 '07 #4

Post your reply

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