Bogus SQL Result

[wizcow2]

Hi, I have a query wich returns the results I want.
I am trying to put it into code, so I copied from the sql pane.
But my result is always 12:00PM.

What am I doing wrong here?

Dim ld As Date
Dim stsql As String
Dim msg As VbMsgBoxResult

stsql = "SELECT TOP 1 Inventory.StockTakeDate" & _
"FROM Inventory INNER JOIN InventorySub ON Inventory.InventoryID = InventorySub.InventoryID " & _
"WHERE (InventorySub.ItemNo) = [Forms]![part]![Item No] " & _
"ORDER BY Inventory.StockTakeDate DESC;"

stsql = ld
msg = MsgBox("last inventory date was " & ld & " 2003", vbOKOnly)

Tom

 

[namliam]

For 1 i hope this is a snipit from a full set of code cause you will need to open a recordset (if its a snipit, SAY SO!)

stsql = "SELECT TOP 1 Inventory.StockTakeDate" & _
"FROM Inventory INNER JOIN InventorySub ON Inventory.InventoryID = InventorySub.InventoryID " & _
"WHERE (InventorySub.ItemNo) = [Forms]![part]![Item No] " & _
"ORDER BY Inventory.StockTakeDate DESC;"

This cannot be stored into a recordset try:
stsql = "SELECT TOP 1 Inventory.StockTakeDate" & _
"FROM Inventory INNER JOIN InventorySub ON Inventory.InventoryID = InventorySub.InventoryID " & _
"WHERE (InventorySub.ItemNo) = " & [Forms]![part]![Item No] & _
"ORDER BY Inventory.StockTakeDate DESC;"

Regards

The Mailman

 

[wizcow2]

Mailman

This code is going to be a snipit, but right now I'm trying to make it work just off of a button click.

Private Sub Command85_Click()

Dim ld As Date
Dim stsql As String
Dim msg As VbMsgBoxResult

stsql = "SELECT TOP 1 Inventory.StockTakeDate" & _
"FROM Inventory INNER JOIN InventorySub ON Inventory.InventoryID = InventorySub.InventoryID " & _
"WHERE (InventorySub.ItemNo) = " & [Forms]![part]![Item No] & _
"ORDER BY Inventory.StockTakeDate DESC;"

stsql = ld
msg = MsgBox("last inventory date was " & ld & " 2003", vbOKOnly)

End Sub

Thanks. It still is returning 12:00 AM

Tom

 

[AnotherWayne]

wizcow,

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim stsql As String


stsql = "SELECT TOP 1 Inventory.StockTakeDate" & _
        "FROM Inventory " & _
        "INNER JOIN InventorySub ON Inventory.InventoryID = InventorySub.InventoryID " & _
        "WHERE (InventorySub.ItemNo) = " & [Forms]![part]![Item No] & _
        "ORDER BY Inventory.StockTakeDate DESC;"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strsql)
If rst.EOF And rst.BOF Then
   MsgBox("There is no stock!")
Else
   MsgBox("last inventory date was " & rst!StockTakeDate & " 2003", vbOKOnly)
End If

Why the 2003?

Wayne

 

[wizcow2]

Oops!

The 2003 wasn't supposed to still be in there.
Thank you for the reply.

I am now getting a Run Time Error 3075
syntax error (missing operator) in query expression

and hangs on

Set rst = dbs.OpenRecordset(strSQL)


Tom

 

[AnotherWayne]

Tom,

You need to add InventorySub in:

"FROM Inventory, InventorySub " & _

Another Wayne

 

[namliam]

Wayne, the InventorySub is allready there After the INNER JOIN part....

WizCow's problem is in the SQL...
stsql = "SELECT TOP 1 Inventory.StockTakeDate" & _
"FROM Inventory " & _
"INNER JOIN InventorySub ON Inventory.InventoryID = InventorySub.InventoryID " & _
"WHERE (InventorySub.ItemNo) = " & [Forms]![part]![Item No] & _
"ORDER BY Inventory.StockTakeDate DESC;"

He is missing a space before the ORDER BY command... rusulting in WHERE (InventorySub.ItemNo) = 1ORDER BY Inventory.StockTakeDate DESC

Thus the error.... add a space...
also you might need to add a rst.movefirst (not sure)

Regards