Solved

Access 2003 - SQL statement syntax in VBA code

Posted on 2006-11-10
3
427 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
Comment Utility
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
Comment Utility
{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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

771 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now