Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL troubleshooting (Access03)

    I have the following code that is suppose to append three fields from the tblCertificationsDates to tblCombinedCerts. The strWhere compiles what classes I want selected. I run a make table SQL query before this code to capture data from another table and the strWhere works properly.

    ' SQL for append query
    strSQL = "INSERT INTO tblCombinedCerts ( LearnerID, ClassName, DateOfClassStart )" & _
    "SELECT LearnerID, Type, Date" & _
    "FROM tblCertificationDates WHERE " & strWhere
    ' Execute it
    DoCmd.RunSQL strSQL

    Which gives me a 3075 runtime error code: "Syntax error (missing operator) in query." I am just starting to use more SQL in my work and I don't see the error. I actually created the query in the QBE view then switched to SQL and copied that to VBA and added the line breaks. It stops at the DoCmd.

    Any help and teaching is appreciated. Thank you. Fay

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL troubleshooting (Access03)

    One problem you have is a missing space between Date and FROM when the string is concatenated. That is typically the kind of thing that gives you the missing operator error.

    Date" & _
    "FROM

    try

    Date " & _
    "FROM
    Charlotte

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

    Re: SQL troubleshooting (Access03)

    Okay that worked, but....

    I now get a parameter box asking for ClassName

    With the above code I am inserting Type into ClassName and Date into DateOfClassStart. The date isn't objected to but nothing is appended to first table. Here is the two queries as they stand now.

    ' SQL for make-table query
    strSQL = " SELECT tblRegistrationLearner.LearnerID, tblClasses.ClassName, " & _
    "tblRegistrationLearner.ClassNumber, tblRegistrationLearner.ClassID, " & _
    "tblRegistrationLearner.CancelledNoShow, tblRegistrationLearner.DateTimeRegistered, " & _
    "tblSession.DateOfClassStart,tblRegistrationLearne r.ISDateOfClassStart, " & _
    "tblRegistrationLearner.Grade " & _
    "INTO tblCombinedCerts FROM (tblClasses INNER JOIN tblSession ON " & _
    "tblClasses.ClassID = tblSession.ClassID) INNER JOIN tblRegistrationLearner ON " & _
    "tblSession.ClassNumber = tblRegistrationLearner.ClassNumber WHERE " & strWhere
    ' Execute it
    DoCmd.RunSQL strSQL


    ' SQL for append query
    strSQL = "INSERT INTO tblCombinedCerts ( LearnerID, ClassName, DateOfClassStart )" & _
    "SELECT LearnerID, Type, Date " & _
    "FROM tblCertificationDates WHERE " & strWhere
    ' Execute it
    DoCmd.RunSQL strSQL

    Thank you for the help. Fay

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

    Re: SQL troubleshooting (Access03)

    If you open tblCombinedCerts in design view after running the make table query, is ClassName a field name, or something like tblClasses_ClassName?

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

    Re: SQL troubleshooting (Access03)

    It is ClassName as it should be. Thanks. Fay

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

    Re: SQL troubleshooting (Access03)

    What if you use

    strSQL = "INSERT INTO tblCombinedCerts ( LearnerID, ClassName, DateOfClassStart ) " & _
    "SELECT LearnerID, Type AS ClassName, [Date] AS DateOfClassStart " & _
    "FROM tblCertificationDates WHERE " & strWhere

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

    Re: SQL troubleshooting (Access03)

    I'm afraid we can't solve this without seeing a stripped down copy of the database.

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

    Re: SQL troubleshooting (Access03)

    That didn't work, same problem. I also put [ ]s around Type and that didn't work. Thanks Fay

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

    Re: SQL troubleshooting (Access03)

    I will send a stripped down version as soon as I can. I did take the code and placed it in a query removing the VBA extraneous material. It runs like it should. Hummm.

    Fay

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

    Re: SQL troubleshooting (Access03)

    Here is the stripped down version. Thanks for your help. Fay

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

    Re: SQL troubleshooting (Access03)

    Aha. Your strWhere refers to ClassName, but that is not a field name in tblCertificationDates. This is easily remedied: insert the following line in the Certifications function AFTER running the make table query, but BEFORE running the append query:

    strWhere = Replace(strWhere, "ClassName", "Type")

    This will change the field name ClassName in strWhere to Type.

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

    Re: SQL troubleshooting (Access03)

    Okay I no longer get the parameter box. But if I select all of the departments, credentials, and the BLS* classes only one record is appended. 42 records should have been appended. The BLS renewal class was the only one appended and there is only one of those in the tblCertificationDates. So I am thinking there is an issue with the strWhere statement.

    I thought it may have related to the fact DateOfClassStart wasn't in the tblCertificationDates table so I added that and it didn't work.

    Any ideas? You got me in over my head again. Fay

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

    Re: SQL troubleshooting (Access03)

    The make-table query creates 55 records; the append query only one because the only Type in tblCertificationDates that corresponds to an item in the ClassPicker list box is "BLS Healthcare Provider Renewal". There are no records with Type equal to "BLS for Healthcare Providers" or "BLS Instructor Renewal Course".

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

    Re: SQL troubleshooting (Access03)

    The disconnect was that the name for the classes was slightly different. Have corrected the tblCertificationsDates and it is working correctly. Need to do a little house cleaning in the background. Thank you.

    Fay

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

    Re: SQL troubleshooting (Access03)

    How do I set Max date in SQL?
    I tried AS MaxOf

    strSQL = "SELECT * " & _
    "INTO tblCombinedCertsDidDidnt " & _
    "FROM tblCombinedCerts " & _
    "AS MaxOfDateOfClassStart " & strWhere

    Thank you for your help. Fay

Page 1 of 2 12 LastLast

Posting Permissions

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