Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2002
    Posts
    21

    Question Unanswered: Filtering Latest Part's Cost with SQL?

    Hello,

    I'm trying to write a products form that has the cost of the part, but I want to make the field dynamic (I want it to look up the last time the part was purchased and display that price)

    I have a products table and a purchases table. The purchases table stores every time each part was ordered and for what price.

    How do i pull that data into the form for the products? I guess i could make a query and try and filter it, but i'm not sure how to filter a query and place the result into a textbox in VBA

    I think the best way would be to use SQL, but can anyone help me with the SQL statement? I'm a bit lost!

    Thanks in advanced!
    jrale

  2. #2
    Join Date
    Aug 2002
    Posts
    21
    I also wanted to do the same thing with the date the part was purchased.

  3. #3
    Join Date
    Aug 2002
    Posts
    21
    perhaps this won't work? I was reading somewhere (can't remember where now) that said you can't use SELECT statements in the docmd.runsql. Is this true?

  4. #4
    Join Date
    Aug 2002
    Posts
    21
    This is what I tried to do, but it comes up with an error. (Run-time error '2342': A RunSQL action requires an argument consisting of an SQL statement.)

    Code:
    Dim dCost As String
    Dim currentPID As String
    Dim strSQL As String
    currentPID = ProductID
    
    strSQL = "SELECT TransactionDate, ProductID, UnitPrice FROM Inventory Transactions WHERE ProductID='" & currentPID & "';"
    
    DoCmd.RunSQL (strSQL)
    I've been trying all kinds of ways to do this, but i can't seem to figure it out... any help would be extremely appreciated!

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    jrale,

    Are you trying to somehow take the results returned by that query and
    populate fields on the form? You could probably do that using the
    Dlookup, Dmax... functions. I never use those, so I can't help you there, though.

    However, you could also use VBA and a recordset. Do you know if
    you are using DAO or ADO?

    Mark
    Inspiration Through Fermentation

  6. #6
    Join Date
    Aug 2002
    Posts
    21
    This is the code I'm using... it seems to work.

    Code:
    Dim currentPID As String
    currentPID = ProductID
    Dim strSQL As String
    Dim Db As Database, Qd As QueryDef
    Set Db = CurrentDb()
    Set Qd = Db.QueryDefs("FindCost")
    strSQL = "SELECT Last([Inventory Transactions].TransactionDate) AS LastOfTransactionDate, Last([Inventory Transactions].UnitPrice) AS LastOfUnitPrice FROM [Inventory Transactions] WHERE [Inventory Transactions].ProductID='" & currentPID & "';"
    Qd.SQL = strSQL
    LastDate = DAvg("[LastOfTransactionDate]", "FindCost")
    Cost = DAvg("[LastOfUnitPrice]", "FindCost")
    Db.Close
    Set Db = Nothing
    Set Qd = Nothing
    Is there anything I should be doing differently, or is this ok?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •