How to Search the Range Value

[jamesnkk]

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 ?

[NeoPa]

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).

[jamesnkk]

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.

[NeoPa]

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 :

Private Sub txtProdID_AfterUpdate()
    Call ComboSource
End Sub
 
Private Sub txtQty_AfterUpdate()
    Call ComboSource
End Sub
 
Private Sub ComboSource()
    Dim strSQL As String, strPrice As String
 
    If Nz(txtProdID, "") = "" Or Nz(txtQty, 0) = 0 Then
        strSQL = ""
    Else
        strPrice = "CCur(Mid(Max(" & _
                   "Format([Pur Qty],'0000000000') & " & _
                   "[Price]),11)) "
        strSQL = "SELECT ProductID," & _
                        "SupplierID," & _
                        strPrice & _
                     "FROM tblPrice " & _
                     "WHERE ((ProductID='" & txtProdID & "')" & _
                     "  AND ([Pur Qty]<=" & txtQty & ")) " & _
                     "GROUP BY SupplierID"
    End If
    cboResults.RowSource = strSQL
End Sub