Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Help with SQL Syntax

    Hi,

    I'm having trouble with some SQL syntax I'm using for a recordset. I keep getting the following error when trying to run it:-

    Run-time error '3061':

    Too few parameters. Expected 1.
    Here is the syntax I'm using:-

    Code:
    sSQL = " SELECT DISTINCT QUERCUS_COURSE.COURSE, QUERCUS_COURSE.DESCRIPTION" & _
           " FROM (QUERCUS_PERSON INNER JOIN (QUERCUS_LEARNER INNER JOIN QUERCUS_LEARNING_AIM" & _
           " ON QUERCUS_LEARNER.OBJECT_ID = QUERCUS_LEARNING_AIM.LEARNER)" & _
           " ON QUERCUS_PERSON.OBJECT_ID = QUERCUS_LEARNER.PERSON) INNER JOIN" & _
           " (QUERCUS_MODE_OF_STUDY INNER JOIN (QUERCUS_COURSE INNER JOIN" & _
           " (QUERCUS_STATUS INNER JOIN (QUERCUS_COURSE_INSTANCE INNER JOIN" & _
           " QUERCUS_STUDENT_COURSE_DETAIL ON QUERCUS_COURSE_INSTANCE.OBJECT_ID = QUERCUS_STUDENT_COURSE_DETAIL.COURSE_INSTANCE)" & _
           " ON QUERCUS_STATUS.OBJECT_ID = QUERCUS_STUDENT_COURSE_DETAIL.STATUS)" & _
           " ON QUERCUS_COURSE.OBJECT_ID = QUERCUS_COURSE_INSTANCE.COURSE)" & _
           " ON QUERCUS_MODE_OF_STUDY.OBJECT_ID = QUERCUS_COURSE_INSTANCE.MODE_OF_STUDY)" & _
           " ON QUERCUS_PERSON.OBJECT_ID = QUERCUS_STUDENT_COURSE_DETAIL.PERSON" & _
           " WHERE QUERCUS_COURSE.COURSE Not Like '%HE%'" & _
           " AND QUERCUS_COURSE.COURSE = " & Me!cmboCCode & _
           " AND QUERCUS_LEARNING_AIM.DATA_SET_FORMAT Not In ('2','4','5','7','8')" & _
           " AND QUERCUS_MODE_OF_STUDY.MODE_OF_STUDY Not In ('FULL-TIME_FULL_Y','SANDWICH','FULL-TIME_LESS_T','FTS','04')" & _
           " AND QUERCUS_LEARNING_AIM.GUIDED_LEARNING_HOURS <= '6'" & _
           " AND QUERCUS_COURSE_INSTANCE.ACADEMIC_YEAR =" & [Forms]![frmMenu]![cmboAcademic_Year] & _
           " AND QUERCUS_STATUS.STATUS = 'R'"
    Can anyone help?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    This is a DAO error that can indicate pretty well any syntax problem.

    What is the database object assigned to? Does it use the % wildcard? If not, it will fail. Swap to * if you are querying Access.

    Can you print the content of sSQL to the Immediate window and execute that (e.g. in QA for SQL Server or in a query in Access) and see if it works?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    OK, after running the SQL generated in the intermediate window using the QA, I noticed that on the following line:-

    " AND QUERCUS_COURSE.COURSE = " & Me!cmboCCode & _
    I need to have single quotes around the Me!cmboCCode, one I added these in QA it ran fine. But if I try and but the single quotes aroundthe syntax in VBA it doesn't like it.

    This is probably a noobish question but...



    ...How can I add the quotes around it?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    " AND QUERCUS_COURSE.COURSE = '" & Me!cmboCCode & "'" &_
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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