Solved

Access 2003 - SQL statement syntax in VBA code

Posted on 2006-11-10
3
431 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

756 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