Help with a select statement (1 Viewer)

[keving]

Hello. I am having trouble with a select statement in my code. The select has actually been working for months and then all of a sudden I got a surprise. One of the pieces of data has a single quote imbedded in it and it causes the lookup to fail. Here is the problem.

SELECT * FROM [Training Request Table] Where [Student Last Name]= 'Morel-à-l'Huissier' and [Student First Name]= 'Patrick' ;


As you can see the student last name has a single quote in it which messes up the select. Any ideas?????

Kevin ....

 

[ByteMyzer]

Change the SQL statement to:

SELECT * FROM [Training Request Table] Where [Student Last Name]= "Morel-à-l'Huissier" and [Student First Name]= "Patrick" ;


To implement this statement in Visual Basic (ex. enumerating a recordset):

'rs - RecordSet Object
Set rs = CurrentDb.OpenRecordset("SELECT * FROM [Training Request Table] " _
    & "Where [Student Last Name]= ""Morel-à-l'Huissier"" " _
    & "and [Student First Name]= ""Patrick"";")

 

[keving]

ByteMyzer, your suggestion appears to stop my code from failing but it also appears that I am doing my lookup using a variable name rather than the student name? The following shows how I built the SQL string in VBA. The two commented lines are my original code replaced by your suggestion.

stSql = "SELECT * FROM [Training Request Table]"
stSql = stSql & " Where [Student Last Name]= "" & [InStudentLastName] & "" and"
stSql = stSql & " [Student First Name]= "" & [InStudentFirstName] & "" ;"
' stSql = stSql & " Where [Student Last Name]= '" & [InStudentLastName] & "' and"
' stSql = stSql & " [Student First Name]= '" & [InStudentFirstName] & "' ;"

rstTrainingRequestTable.Open stSql, cnn, adOpenDynamic, adLockOptimistic

I appreciate the help.

Kevin ....

 

[ByteMyzer]

Try this:

stSql = "SELECT * FROM [Training Request Table]"
stSql = stSql & " Where [Student Last Name]= """ & [InStudentLastName] & """ and"
stSql = stSql & " [Student First Name]= """ & [InStudentFirstName] & """ ;"

 

[keving]

That solves the problem. Thanks so much for your help.

Keving

 

[Mile-O]

For each ' substitute ""