need help on sql

[me]

I need help on the correct sql-syntax, in a loop to process one recordset by
a number from another recordset.
In testing before buiding the loop, I cannot get the sql right. Only the
last strSQL gives the desired results. What is the correct sql to implement
a variable like rstValid!lngItemID for lngItemID and not a fixed number? I
keep getting error 3061.

Thanks



Sub test()
Dim db As DAO.Database
Dim rstValid As DAO.Recordset
Dim rstTemp As DAO.Recordset
Dim strSQL As String
Dim lngTemp As Long

Set db = DBEngine(0)(0)
Set rstValid = db.OpenRecordset("tblValid")
lngTemp = rstValid!lngItemID
' strSQL = "SELECT * FROM tblBig where tblBig.lngItemID = 15073395"
' strSQL = "SELECT tblBig.lngIndex FROM tblBig INNER JOIN tblValid ON
tblBig.lngItemID = tblValid.lngItemID WHERE (([tblBig]![lngItemID] =
lngTemp))"
' strSQL = "SELECT tblBig.lngIndex FROM tblBig INNER JOIN tblValid ON
tblBig.lngItemID = tblValid.lngItemID WHERE (((tblValid.lngItemID) =
rstValid.lngItemID))"
strSQL = "SELECT tblBig.lngIndex FROM tblBig INNER JOIN tblValid ON
tblBig.lngItemID = tblValid.lngItemID WHERE (((tblValid.lngItemID) =
15073395))"
Set rstTemp = db.OpenRecordset(strSQL)
End Sub

 

[TC]

Set db = DBEngine(0)(0)

That's fine, you're getting a reference to the current database.

Set rstValid = db.OpenRecordset("tblValid")

That's fine, you've opened a recordset which is based on a table in the
current database.

lngTemp = rstValid!lngItemID

That is *not* fine, because, your recordset does not have any
particular order. Access might return a different record as "first"
today, to what it returns tomorrow. It does not make sense to take a
value from the first record in a recordset, unless you have /enforced/
an order for that recordset - by, for example, basing the recordset on
a SELECT statement which uses an ORDER BY clause to set the right
order.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[me]

The order you mention seems not really relevant to the problem. Sorry, let
me
explain more that in fact I defined the variable just to test the acceptance
of the variable in the sql-syntax. When I run the code, variable lngTemp has
the value of 15073395, which is fine for the test on hand. When I put that
number hardcoded in
the sql-syntax to get a subset of data from tblBig for just that number, the
code runs fine. But just for that hardcoded number. The problem is, that I
want a variable e.g. lngTemp in the sql-syntax instead. So I can construct a
loop for all the Itemnumbers besides 15073395. But substituting the number
for a variable in whatever way I try gives error 3061 when the line
Set rstTemp = db.OpenRecordset(strSQL) is executed.
What is the correct sql-syntax to get my subset ?

 

[John Vinson]

' strSQL = "SELECT tblBig.lngIndex FROM tblBig INNER JOIN tblValid ON
tblBig.lngItemID = tblValid.lngItemID WHERE (((tblValid.lngItemID) =
rstValid.lngItemID))"

You need to concatenate the VALUE of the variable rather than its
NAME:

strSQL = "SELECT tblBig.lngIndex FROM tblBig INNER JOIN tblValid ON
tblBig.lngItemID = tblValid.lngItemID WHERE (((tblValid.lngItemID) = "
& rstValid.lngItemID & "))"

However, on looking at it, this query makes no sense: you're already
selecting only values from tblBig which match tblValid on lngItemID in
the JOIN clause; the WHERE clause does nothing that hasn't been done
already! Could you explain what you're trying to accomplish??

John W. Vinson[MVP]

 

[me]

Yes, I can explain. Sorry for not being clear enough.
Every line in the test-code concerning a sql query that is commented out is
a line that doen not work. I am just in need of the right query to get my
subset. I thought it would be helpfull in presenting some thinhs I tried
already without finding a mistake. Just redundancy in testcode.
As for now, I found a solution based on a filter, that does the trick:

rstValues.Filter = "[lngItemID] = " & Str(Nz(rstValid!lngItemID, 0))
'sort it
rstValues.Sort = "[lngIndex] asc"
'and store it in recordset
Set rstTemp = rstValues.OpenRecordset

However, I would still appreciate to find out which error I made in the sql.

 

[John Vinson]

However, I would still appreciate to find out which error I made in the sql.

Having the name of the criterion inside the quotes, rather than
concatenating the value of the criterion, I would guess. Since I
couldn't tell from your example which of the half-dozen tries you
meant, it's hard to be sure!

John W. Vinson[MVP]

 

[me]

Thanks. Lessons learned.

sql.

Having the name of the criterion inside the quotes, rather than
concatenating the value of the criterion, I would guess. Since I
couldn't tell from your example which of the half-dozen tries you
meant, it's hard to be sure!

John W. Vinson[MVP]