Using Variables in a WHERE clause

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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&'))")
 
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
 
TheUbe said:
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&'))")
Click to expand...

Your syntax is wrong. Try this:

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