Solved

Access 2003 - SQL statement syntax in VBA code

Posted on 2006-11-10
3
429 Views
Last Modified: 2008-02-01
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.


0
Comment
Question by:sabrina_spillane
3 Comments
 
LVL 33

Accepted Solution

by:
hongjun earned 100 total points
ID: 17915065
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
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 25 total points
ID: 17915076
{Knee-jerk reaction}  Whack the single quote marks within your Like statement
sqry = sqry & " WHERE (((tblPersonnel.Codes) Like "*Ctr*"))"
0
 
LVL 8

Expert Comment

by:Jillyn_D
ID: 17915088
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

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question