can I not put a where clause in a Transform query?

[Richard Hollenbeck]

The where clause should read a value from an open form. A report is based
on this query but I get the following error when I attempt filter the output
like this:
"The Microsoft Jet database engine does not recognize
'[Forms]![frmSelectCourse]![cboSelectCourse]' as a valid fieldname or
expression."

The combo box and form name are indeed spelled correctly, and the form IS
open. The query is a saved query called "qryTableOfGrades" and the report
is called "rptTableOfGrades."

I don't want a report on every course, just the one that was selected in the
previous form (frmSelectCourse). It works (somewhat) without the where
clause but it gives faulty data. Here's my query so far:

TRANSFORM Sum(studentScores.score) AS SumOfscore
SELECT studentsInCourses.courseCode, courses.courseCode,
courses.courseDescription, [lName] & ", " & [fName] AS [Student Name]
FROM students INNER JOIN (((courses INNER JOIN groups ON courses.courseCode
= groups.courseCode) INNER JOIN studentsInCourses ON courses.courseCode =
studentsInCourses.courseCode) INNER JOIN (activities INNER JOIN
studentScores ON activities.activityID = studentScores.activityID) ON
groups.groupID = activities.groupID) ON (students.studentID =
studentsInCourses.studentID) AND (students.studentID =
studentScores.studentID)
WHERE (((courses.courseCode)=[Forms]![frmSelectCourse]![cboSelectCourse]))
GROUP BY studentsInCourses.courseCode, courses.courseDescription, [lName] &
", " & [fName]
ORDER BY studentsInCourses.courseCode
PIVOT activities.activityDescription;

Ideas? Thanks.
Rich Hollenbeck

 

[John Vinson]

The where clause should read a value from an open form. A report is based
on this query but I get the following error when I attempt filter the output
like this:
"The Microsoft Jet database engine does not recognize
'[Forms]![frmSelectCourse]![cboSelectCourse]' as a valid fieldname or
expression."

You can - but you must use the Query's Parameters collection and add
the expression to it. Right mouseclick in the query design table
background and select Parameters; copy and paste the parameter into
the left column. Specify its datatype in the right column.

This is optional (and a good idea) for any query, but it's required
for Crosstabs.

John W. Vinson[MVP]

 

[Richard Hollenbeck]

You can - but you must use the Query's Parameters collection and add
the expression to it. Right mouseclick in the query design table
background and select Parameters; copy and paste the parameter into
the left column. Specify its datatype in the right column.

This is optional (and a good idea) for any query, but it's required
for Crosstabs.

John W. Vinson[MVP]

Thank you John.

I did what you said and got the following error:

Invalid bracketing of name '[courses].[courseCode] =
[Forms]![frmSelectCourse]![cboSelectCourse]'.

Access help only says, "The specified name either cannot have brackets
around it or the brackets are mismatched. Check your entry to make sure the
brackets are properly matched, and then try the operation again."

 

[John Vinson]

You can - but you must use the Query's Parameters collection and add
the expression to it. Right mouseclick in the query design table
background and select Parameters; copy and paste the parameter into
the left column. Specify its datatype in the right column.

This is optional (and a good idea) for any query, but it's required
for Crosstabs.

John W. Vinson[MVP]

Thank you John.

I did what you said and got the following error:

Invalid bracketing of name '[courses].[courseCode] =
[Forms]![frmSelectCourse]![cboSelectCourse]'.

Access help only says, "The specified name either cannot have brackets
around it or the brackets are mismatched. Check your entry to make sure the
brackets are properly matched, and then try the operation again."

The Parameter should be

[Forms]![frmSelectCourse]![cboSelectCourse]

without the previous expression. If that doesn't work please open the
Query in SQL view and post it.

John W. Vinson[MVP]

 

[Richard Hollenbeck]

The Parameter should be

[Forms]![frmSelectCourse]![cboSelectCourse]

without the previous expression. If that doesn't work please open the
Query in SQL view and post it.

John W. Vinson[MVP]

Thanks John. That did the trick. Also when I opened it in SQL view I
noticed it automatically inserted an extra set of square brackets. When I
removed the extra brackets it worked fine. Thanks a million. Please have a
safe and happy holiday season.

Rich Hollenbeck