Using Variables in a WHERE clause

[TheUbe]

I am trying to use a variable in a SET RST statement in the WHERE clause.
Even though I assign the value, when I run the code the record set returns
zero records. If I type in WHERE (((tblstudents.group) = '6'))")
it works. Any suggestions why the variable fails.


Dim strGroupIn As String
strGroupIn = "6"
Dim Rst As Recordset
Dim MyDb As Database

Set MyDb = CurrentDb()

Set Rst = MyDb.OpenRecordset("SELECT tblstudents.* FROM tblStudents WHERE
(((tblstudents.group) = '&strgroupin&'))")

 

[Nikos Yannacopoulos]

Ken,

strgroupin should be outside the double quotes, so Access understands it is
a variable rather than just part of the string, and treats it as such. Try
this:

strSQL = "SELECT tblstudents.* FROM tblStudents WHERE tblstudents.group = '"
& strgroupin & "'"
'Watch out for wrapping in the post! All in one line
Set Rst = MyDb.OpenRecordset(strSQL)

I have used an extra variable strSQL just to improve readability.

HTH,
Nikos

 

[Brian]

I am trying to use a variable in a SET RST statement in the WHERE clause.
Even though I assign the value, when I run the code the record set returns
zero records. If I type in WHERE (((tblstudents.group) = '6'))")
it works. Any suggestions why the variable fails.


Dim strGroupIn As String
strGroupIn = "6"
Dim Rst As Recordset
Dim MyDb As Database

Set MyDb = CurrentDb()

Set Rst = MyDb.OpenRecordset("SELECT tblstudents.* FROM tblStudents WHERE
(((tblstudents.group) = '&strgroupin&'))")

Your syntax is wrong. Try this:

Set Rst = MyDb.OpenRecordset("SELECT tblstudents.* FROM tblStudents WHERE
(((tblstudents.group) = '" & strgroupin & "'))")