SQL results in VB (1 Viewer)

[wizcow]

I am trying to get the results of this sql statement into a msgbox.
I just get the statement repeated in the msgbox.
What I want is the result of the sql statement.

Private Sub cmdCalc_Click()

    Dim strSQL As String
     
    strSQL = "SELECT TOP 1 Inventory.StockTakeDate" & _
             "FROM Inventory INNER JOIN InventorySub ON Inventory.InventoryID = InventorySub.InventoryID" & _
             "WHERE (InventorySub.ItemNo) = [Forms]![Part]![ItemNo]" & _
             "ORDER BY Inventory.StockTakeDate DESC;"
    
    MsgBox strSQL
    
End Sub

What am I missing here?

Thanks
Tom

 

[=TB=]

Try this

MsgBox (YourRs.Fields("StockTakeDate"))

 

[namliam]

Your not missing much, if you paste the SQL in a "normal" query it will work however in VBA you cannot use the forms bit instide the string

Try this:
"WHERE (InventorySub.ItemNo) = "& [Forms]![Part]![ItemNo]
That will work for numbers

Dates: "WHERE (InventorySub.ItemNo) = #"& [Forms]![Part]![ItemNo] &"#"
Note that the date must be in AMERICAN format (MM/DD/YYYY)
String: "WHERE (InventorySub.ItemNo) = "& chr(34) & [Forms]![Part]![ItemNo] & chr(34)

Good Luck

 

[wizcow]

Thank you for your replys.
Please have a look at what I have here now.
I am getting a run time error 3075

Private Sub cmdCalc_Click()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
     
    strSQL = "SELECT Inventory.StockTakeDate" & _
             "FROM Inventory INNER JOIN InventorySub ON Inventory.InventoryID = InventorySub.InventoryID" & _
             "WHERE (InventorySub.ItemNo) = " & [Forms]![Part]![ItemNo] & _
             "ORDER BY Inventory.StockTakeDate DESC;"
             
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
    
    MsgBox (rs.Fields("stocktakedate"))
    
End Sub

Thanks Tom

 

[Jon K]

You need to separate each continuos line by a space.

strSQL = "SELECT Inventory.StockTakeDate" & _
         " FROM Inventory INNER JOIN InventorySub ON Inventory.InventoryID = InventorySub.InventoryID" & _
         " WHERE (InventorySub.ItemNo) = " & [Forms]![Part]![ItemNo] & _
         " ORDER BY Inventory.StockTakeDate DESC;"

 

[wizcow]

I tried adding the spaces but that didn't quite do it either.

I have simplified my code trying to trace my problem.
Please check this out.

Private Sub cmdCalc_Click()

    Dim strSQL As String
     
    strSQL = "SELECT Inventory.StockTakeDate FROM Inventory ;"

    MsgBox StockTakeDate
    
End Sub

I get an error "Variable Not Defined" and it highlites StockTakeDate.
What I want is a message box with a date in it.
Help!

Tom

 

[wizcow]

I got it!
Thanks for the help!

Private Sub cmdCalc_Click()
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim StockTakeDate As String
           
    strSQL = "SELECT TOP 1 Inventory.StockTakeDate, Inventory.InventoryID, InventorySub.ItemNo, InventorySub.Quantity" & _
            " FROM Inventory INNER JOIN InventorySub ON Inventory.InventoryID = InventorySub.InventoryID" & _
            " WHERE (InventorySub.ItemNo) = " & [Forms]![Part]![ItemNo] & _
            " ORDER BY Inventory.StockTakeDate DESC;"

    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
    
    MsgBox rs!StockTakeDate
    
End Su