sql syntax problem

[pmacdiddie]

I have this line of code where I cant get the syntax quite right. Can
anyone offer a fix?

sSQL = "SELECT Job_Num, Salesman FROM tblJobs WHERE Salesman = " _
& '" & recGroups.Fields("EMP_ID").Value & "' & " ORDER
BY Salesman Asc"

The debugger highlighs the first ' on the second row. I have tried
many variations. The value of EMP_ID is string.

Thanks so much.

Preston

 

[Sylvain Lafontaine]

sSQL = "SELECT Job_Num, Salesman FROM tblJobs WHERE Salesman = " _
& "'" & recGroups.Fields("EMP_ID").Value & "'" & " ORDER BY
Salesman Asc"

or:

sSQL = "SELECT Job_Num, Salesman FROM tblJobs WHERE Salesman = " _
& Chr(39) & recGroups.Fields("EMP_ID").Value & Chr(39) & "
ORDER BY Salesman Asc"

or:

sSQL = "SELECT Job_Num, Salesman FROM tblJobs WHERE Salesman = '" _
& recGroups.Fields("EMP_ID").Value & "' ORDER BY Salesman
Asc"

 

[pmacdiddie]

Did it work as suggested by Sylvain Lafontaine? In case you don't understand,
you have misplaced the single quote, it must inside the double quotes.

Thanks, I ended up with a Chr(34) with the desired results.

 

[Ken Sheridan]

You may not be aware that two contiguous double-quote characters is also
interpreted as a literal double-quote character:

sSQL = "SELECT Job_Num, Salesman " & _
"FROM tblJobs " & _
"WHERE Salesman = """ & _
recGroups.Fields("EMP_ID").Value & _
""" ORDER BY Salesman ASC"

This is equivalent to using Chr(34) and is preferable to using a
single-quote character as a text delimiter where the values might contain
apostrophes, e.g. Cináed O'Siridean (my name in its original unanglicized
form).

Ken Sheridan
Stafford, England