Results 1 to 7 of 7

Thread: SQL (Access03)

  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL (Access03)

    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" " & _

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL (Access03)

    If you want to include quoted strings within a quoted string, you must either use single quotes or double the double quotes. Try this:
    <code>
    "WHERE (((tblLearners.Inactive)=0) AND ((tblLearners.Credential)='RN' " & _
    "OR (tblLearners.Credential)='LPN') AND ((tblLearnerDepartments.Status)='Perdiem"'" & _
    </code>
    or
    <code>
    "WHERE (((tblLearners.Inactive)=0) AND ((tblLearners.Credential)=""RN"" " & _
    "OR (tblLearners.Credential)=""LPN"") AND ((tblLearnerDepartments.Status)=""Perdiem"" " & _</code>

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL (Access03)

    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 <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> worth

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> I am aware Hans is writing as I type this so will probably have a diffferent take than me
    Jerry

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL (Access03)

    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""));"

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL (Access03)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL (Access03)

    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.

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL (Access03)

    Thanks that was what I figured you would say. Fay

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •