Solved Apostrophe in statement giving missing operator

oxicottin

Learning by pecking away....
Local time
Today, 08:10
Joined
Jun 26, 2007
Messages
891
Hello, I have a strSQL string in a function and I don't want it to look for certain words. One of my words has an apostrophe and I was wondering how to fix that? Do you enclose the ' with a "'"

Code:
strSQL = strSQL & "WHERE tbl_YearCalendar.EmployeeID = " & empID & " AND tbluAbsenceCodes.AbsenceCode Not In ('PTO','PTO/F','PTO/M','FMLA','F','JD','ML','FLEX','FD/FML','PPL','PC','DL','ELE/PP','ET/PP','EA/PP','EA/C'd') "
 
no, you repeat it (i.e. two single quotes)

not in ("abc''d",......
 
One of my words has an apostrophe and I was wondering how to fix that?
use double quotes twice on each side of the value with apostrophe
Code:
strSQL = strSQL & "WHERE ... Not In ('ELE/PP', ""EA/C'd"", 'JD', ... )"
 
Single quotes (apostrophes) are appropriate except where you want to embed in string: 'EA/C'd'

Apostrophe is a special character for SQL and if you want it embedded and treated as text, it must be handled differently - doubling (aka, escaping) is one way: 'EA/C''d'. This SQL string will construct with both apostrophes but when the SQL executes, the two apostrophes will 'escape' and be treated as a single character.

Eugenes's suggestion for doubled quote marks is also valid. This is escaping the quote mark so the SQL statement will construct with one quote mark and the solo apostrophe will be delimited by quote marks instead of more aspostrophes which confuses the SQL engine.
 
Last edited:
Thanks everyone for the help that worked.....
 
you can also create a Table for those Absent Code:

tblAbsCode:

AbsCode (Autonumber)
CodeDesc (short string)


then put those code in the table (without enclosing in quotes), ex:

CodeDesc
-----------
PTO
PTO/F
..
..
EA/PP
EA/C'd
..


now modify your query string:



strSQL = strSQL & "WHERE tbl_YearCalendar.EmployeeID = " & empID & " AND tbluAbsenceCodes.AbsenceCode Not In (SELECT [CodeDesc] FROM tblAbsCode)"

the table can be handy so you can add more absense code to it and you don't need to modify the SQL string everytime.
 
you can also create a Table for those Absent Code:

tblAbsCode:

AbsCode (Autonumber)
CodeDesc (short string)


then put those code in the table (without enclosing in quotes), ex:

CodeDesc
-----------
PTO
PTO/F
..
..
EA/PP
EA/C'd
..


now modify your query string:



strSQL = strSQL & "WHERE tbl_YearCalendar.EmployeeID = " & empID & " AND tbluAbsenceCodes.AbsenceCode Not In (SELECT [CodeDesc] FROM tblAbsCode)"

the table can be handy so you can add more absense code to it and you don't need to modify the SQL string everytime.
Interesting and good idea.... Thanks
 

Similar threads

Users who are viewing this thread

Back
Top Bottom