Solved

Access 2003 - SQL statement syntax in VBA code

Posted on 2006-11-10
3
433 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 66

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 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