Correct Syntax for Variables inside brackets for SQL Statements

[mous]

Hi

I have an SQL statement that ends:-

& " WHERE (((???) =-1))"

and I'm trying to insert the varible EMAWeek where it is ???. I have this variable in other places of the SQL and works great but it won't work with the brackets. I have an error mesage saying 2001. You cancelled the operation.

Any ideas would be great.

Thanks

 

[Banana]

Are you building the SQL in a query or in VBA?

if VBA, then:

& " WHERE (((" & MyVariable & ) = -1))"

 

[mous]

Thanks, but I think there's still something missing. Here the full statement:

Me.RecordSource = "SELECT tblEMAApplications.EStudentID, tblEMAApplications.C2, tblLookupCampus.CampusDesc, tblAccessStudents.StudSurname, tblAccessStudents.StudFirstName, tblAccessStudents.StudDOB, EMAWeek, EMADesc" _
& " FROM (((qryIdentifyEMALearnersfromLSR INNER JOIN tblAccessStudents ON qryIdentifyEMALearnersfromLSR.StudCollegeIDRef = tblAccessStudents.StudCollegeIDRef) LEFT JOIN tblEMAApplications ON tblAccessStudents.StudCollegeIDRef = tblEMAApplications.EStudentID) LEFT JOIN tblEMAAttendanceDetails ON tblEMAApplications.EStudentID = tblEMAAttendanceDetails.AttStudentID) LEFT JOIN tblLookupCampus ON tblAccessStudents.StudWB = tblLookupCampus.CampusID" _
& " WHERE (((" & EMAWeek &) = -1))" _
& " ORDER BY tblAccessStudents.StudSurname"

 

[Banana]

You forgot one more "-

& " WHERE (((" & EMAWeek & [color=red]"[/color]) = -1))" _

Also, you always need a ';' at end of your SQL statement.

 

[mous]

Thanks, it's now showing me the standard "Enter Parameter Value" box.

 

[mous]

Sorry, Yes it does work I forgot the '' on the other two parameters. Thanks a lot. Have a good weekend !!