Update Set Statement problems

[patriciashoe]

Everything I read indicates the following statement should work. What happens is that any field after the where clause gets treated as a parameter by Access. Even if I supply the data the query fails. Any ideas? Thanks, Patti

UPDATE teacherresources SET teacherresources.Strudent_enroll = strudent_enroll/(SELECT sum(enroll_data) FROM enrollment WHERE teacherresources.trSchool_id = enrollment.erSchool_id and year='2007-2008')*(SELECT sum(enroll_data) FROM enrollment WHERE teacherresources.trSchool_id = enrollment.erSchool_id and year='2008-2009')
WHERE (((teacherresources.tryear)='2008-2009') AND ((teacherresources.trschoolid)=13));

[Fiddler2]

Everything I read indicates the following statement should work. What happens is that any field after the where clause gets treated as a parameter by Access. Even if I supply the data the query fails. Any ideas? Thanks, Patti

UPDATE teacherresources SET teacherresources.Strudent_enroll = strudent_enroll/(SELECT sum(enroll_data) FROM enrollment WHERE teacherresources.trSchool_id = enrollment.erSchool_id and year='2007-2008')*(SELECT sum(enroll_data) FROM enrollment WHERE teacherresources.trSchool_id = enrollment.erSchool_id and year='2008-2009')
WHERE (((teacherresources.tryear)='2008-2009') AND ((teacherresources.trschoolid)=13));

What is the whack "/" after "strudent_enrol l on the first line? Also, usually when you get a prompt for a parameter like that, it's because you've misspelled a word in the string. Do you really have a field named "strudent"? Next to this, I'd suggest pulling the fields into a query grid to get the SQL straight for Access as its version of SQL can be a little different than ANSI.