SQL (Access03)

Topic

New Reply

I need syntax assistance. I get an error message that it is expecting an end of line message at “RN” I tried double quotes and parathesis around ((tblLearners.Credential)=”RN”

How should items like this suppose to be written. Thank you. Fay

“WHERE (((tblLearners.Inactive)=0) AND ((tblLearners.Credential)=”RN” ” & _
“OR (tblLearners.Credential)=”LPN”) AND ((tblLearnerDepartments.Status)=”Perdiem” ” & _

Reply | Quote

Replies

If you want to include quoted strings within a quoted string, you must either use single quotes or double the double quotes. Try this:

"WHERE (((tblLearners.Inactive)=0) AND ((tblLearners.Credential)='RN' " & _
"OR (tblLearners.Credential)='LPN') AND ((tblLearnerDepartments.Status)='Perdiem"'" & _

or

"WHERE (((tblLearners.Inactive)=0) AND ((tblLearners.Credential)=""RN"" " & _
"OR (tblLearners.Credential)=""LPN"") AND ((tblLearnerDepartments.Status)=""Perdiem"" " & _

Reply | Quote

Got the above problem working. Thank you very much. I now want to covert several queries to SQL, which I did, but… Two of them are select queries which the computer quickly responded with a Run-time error 2342. So I now know I can’t run a straight Select query, I need an action query. Here are the three different queries that I was trying to run. Because of the 2342 error message I tried to combine them, but that isn’t going to work. Because of trying to call strSQL2 in the strSQL3 query. Can you provide an approach? Other wise I will remain with the query grid and continue to clutter the database with queries. Thank you. Fay

strSQL1 = “SELECT tblClasses.ClassID, tblLearners.LearnerID FROM tblClasses, tblLearners ” & _
“WHERE (((tblClasses.ClassID)=116 Or (tblClasses.ClassID)=118 Or (tblClasses.ClassID)=123 ” & _
“Or (tblClasses.ClassID)=126 Or (tblClasses.ClassID)=133 Or (tblClasses.ClassID)=134 ” & _
“Or (tblClasses.ClassID)=135)) ”

strSQL2 = “SELECT tblLearners.LearnerID, tblLearners.LastName, tblLearners.Nickname, ” & _
“tblLearners.Inactive, tblLearners.Credential, tblLearnerDepartments.PerDiem2Unit, ” & _
“tblLearnerDepartments.Status, tblLearnerDepartments.StartDate ” & _
“FROM tblLearners INNER JOIN tblLearnerDepartments ” & _
“ON tblLearners.LearnerID = tblLearnerDepartments.LearnerID ” & _
“WHERE (((tblLearners.Inactive)=0) AND ((tblLearnerDepartments.StartDate)<#1/1/2007#)) "

strSQL3 = "SELECT strSQL1.LearnerID, strSQL1.ClassID, " & _
"strSQL2.LastName, strSQL2.Nickname, strSQL2.Inactive, strSQL2.Credential, " & _
"strSQL2.Status, tblClasses.ClassName, tblCompetencyResultYear.ISDateOfClassStart, " & _
"tblCompetencyResultYear.Grade, tblCompetencyResultYear.ProficiencyGrade " & _
"INTO tblCompetencyResultYear1 " & _
"FROM ((qryCompetenciesResult LEFT JOIN tblCompetencyResultYear " & _
"ON (qryCompetenciesResult.LearnerID = tblCompetencyResultYear.LearnerID) " & _
"AND (qryCompetenciesResult.ClassID = tblCompetencyResultYear.ClassID)) " & _
"INNER JOIN tblClasses ON qryCompetenciesResult.ClassID = tblClasses.ClassID) " & _
"INNER JOIN qryCompetencyResultsA ON qryCompetenciesResult.LearnerID = qryCompetencyResultsA.LearnerID " & _
"WHERE (((qryCompetencyResultsA.Inactive)=0) AND ((qryCompetencyResultsA.Credential)=""RN"" " & _
"Or (qryCompetencyResultsA.Credential)=""LPN""));"

Reply | Quote

strSQL1 and strSQL2 are text strings, not queries, so for example

SELECT strSQL1.LearnerID …

makes no sense. I’d just create the necessary queries in the database window. This has the additional advantage that Access will optimize the execution of queries that are stored in the database window. It cannot optimize the execution of SQL strings.

Reply | Quote

Thanks that was what I figured you would say. Fay

Reply | Quote

Hi Fay

I was originally taught using Oracle Syntax SQL and since then I have found it really helps me with Microsoft SQL. I always write my SQL in Access in my Oracle way and let Access change it. I have a habit (good IMHO) of giving the tables aliases, like so:

Select *

From tblLearners a, tblLearnerDepartments b

WHERE
a.Inactive=0

AND

a.Credential=”RN”

OR

a.Credential=”LPN”

AND

b.Status=”Perdiem”

Note I break it into lines and i can read it more carefully…my worth

I am aware Hans is writing as I type this so will probably have a diffferent take than me

Reply | Quote

Hi Jerry thanks for responding. I am so shaky with SQL as it is. So trying to learn another version/approach is more than I have time for with my other responsibilities. But I do appreciate your response. Fay

Reply | Quote