OpenRecordset 1

[Javamahn]

Is it possible to use db.OpenRecordSet with a Select statement that includes Inner Joins? I am trying to pull a record using the SELECT statement I created using the Query design wizard in Access 2000. below is the statement I have from the wizard. What steps do I need to perform so I can intergrate this code into the vbCode?

'---------------- Start SQL Statement Here -----------------

SELECT Sum(nz([ProductTransactions].[ItemsReceived])-nz([ProductTransactions].[ItemsLost])-nz([ProductTransactions].[ItemsSold])) AS OnHand FROM Products INNER JOIN ProductTransactions ON [Products].[ProductID]=[ProductTransactions].[ProductID]
WHERE ((([Products].[SerialNumber])=[Forms]![Transactions]![TxtProdChkOut]));


Any Help will surely be appreciated

 

[RickSpr]

You certainly can use an inner join with OpenRecordset! You can also use an outer join, a sub-SELECT (IN predicate), a union query, or any other SQL statement that returns a result set.

Just put the whole statement in quotes and use it as the first argument. (Or, put it in a Const or variable and use that, for better readability.) Rick Sprague

 

[RickSpr]

One more thing, Javamahn, in case you do this again in the future. If you SQL statement from the query happens to contain any quote characters (&quot, you either need to double those before you wrap the SQL statement in its own quotes, or you need to translate them to apostrophes ('). I should have mentioned that, but it didn't occur to me since you didn't have any text literals in your SQL code. Rick Sprague