Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2008
    Posts
    10

    Unanswered: Need to add a parameter to sql statment

    I created the following query in MS Query (Excel 2007) and it runs without error.
    --------------------------------------------------
    SELECT
    teachers.lastfirst,
    courses.course_name,
    pgfinalgrades.finalgradename,
    sections.Course_Number,
    sections.Section_Number,
    substring(sections.expression,1,1),
    substring(sections.expression,3,1),
    students.lastfirst,
    cast(students.Student_Number as int),
    pgfinalgrades.percent,
    pgfinalgrades.grade

    From CC
    left join pgfinalgrades on cc.studentid = pgfinalgrades.studentid
    and cc.sectionid = pgfinalgrades.sectionid
    and pgfinalgrades.finalgradename = 'T1'
    inner join students on cc.studentid = students.id
    inner join sections on cc.sectionid = sections.id
    inner join courses on sections.course_number = courses.course_number
    inner join teachers on sections.teacher = teachers.id

    WHERE
    cc.schoolid = 303
    and students.enroll_status = 0
    and sections.termid in (1800, 1801)

    ORDER BY
    teachers.lastfirst,
    courses.course_name,
    sections.expression,
    students.lastfirst
    ----------------------------------------------

    I'd like to have cc.schoolid (in the WHERE clause) to be a prompted parameter where the query is executed.

  2. #2
    Join Date
    Dec 2003
    Location
    Ottawa, Canada
    Posts
    72
    First you create a variable that will be fed by a cell

    varParameter=Sheets("SoandSo").Range("A1").Value

    in the SQL string you replace 303 by ccschoolid= " & varRarameter & " and students

    See my post today about SQL in VBA
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick
    www.excel-examples.com

Posting Permissions

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