Access 2003 - SQL statement syntax in VBA code

Hi All,

I have the following query that works when i build it as an access query but when i insert into the VBA code i have issues with the Where Clause because of all the quotations that is in it. I was just wondering if ye can see what i am missing.

This is the query buile in access with the sql view
SELECT tblPersonnel.CompanyID,
tblPersonnel.Codes, [FirstName] & " " & IIf(IsNull([MiddleInitial]),"",[MiddleInitial] & ". ") & [LastName] AS CCName, People.PeopleEmail AS Email, tblCompany.CompanyName
FROM tblCompany INNER JOIN (tblPersonnel INNER JOIN People
ON tblPersonnel.PeopleId = People.PeopleId)
ON tblCompany.CompanyID = tblPersonnel.CompanyID
WHERE (((tblPersonnel.Codes) Like "*'Ctr'*"))
ORDER BY tblPersonnel.LastName, tblPersonnel.FirstName;

Here is the SQL in VBA code and where i  am having a problem with is the where clause :  "*'Ctr'*" this section


sqry = "SELECT tblPersonnel.CompanyID,"
sqry = sqry & " tblPersonnel.Codes, [FirstName] & " " & IIf(IsNull([MiddleInitial]),"",[MiddleInitial] & ". ") & [LastName] AS CCName,"
sqry = sqry & " People.PeopleEmail AS Email, tblCompany.CompanyName,"
sqry = sqry & " FROM tblCompany INNER JOIN (tblPersonnel INNER JOIN People"
sqry = sqry & " ON tblPersonnel.PeopleId = People.PeopleId)"
sqry = sqry & " ON tblCompany.CompanyID = tblPersonnel.CompanyID"
sqry = sqry & " WHERE (((tblPersonnel.Codes) Like "*'Ctr'*"))"
sqry = sqry & " ORDER BY tblPersonnel.LastName, tblPersonnel.FirstName;"

it is throwing an syntax error in the where clause.


sabrina_spillaneAsked:
Who is Participating?
 
hongjunConnect With a Mentor Commented:
try this


sqry = "SELECT tblPersonnel.CompanyID,"
sqry = sqry & " tblPersonnel.Codes, [FirstName] & " " & IIf(IsNull([MiddleInitial]),"",[MiddleInitial] & ". ") & [LastName] AS CCName,"
sqry = sqry & " People.PeopleEmail AS Email, tblCompany.CompanyName,"
sqry = sqry & " FROM tblCompany INNER JOIN (tblPersonnel INNER JOIN People"
sqry = sqry & " ON tblPersonnel.PeopleId = People.PeopleId)"
sqry = sqry & " ON tblCompany.CompanyID = tblPersonnel.CompanyID"
sqry = sqry & " WHERE (((tblPersonnel.Codes) Like '*Ctr*'))"
sqry = sqry & " ORDER BY tblPersonnel.LastName, tblPersonnel.FirstName;"
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
{Knee-jerk reaction}  Whack the single quote marks within your Like statement
sqry = sqry & " WHERE (((tblPersonnel.Codes) Like "*Ctr*"))"
0
 
Jillyn_DCommented:
Hi sabrina_spillane,

It doesn't like your quotes.  You may have to use Chr() function to get this to work.  Are you familiar with that?

> sqry = sqry & " WHERE (((tblPersonnel.Codes) Like "*'Ctr'*"))"

Good luck!
~Jillyn
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.