SQL Query in 2007

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alexey - 19 Nov 2007 12:07 GMT

Hi I am running the SQL query below in Access 2002/2003 without any issues,
but in Access 2007 it comes up with a 3139 Syntax error in the PARAMETER
clause.

Has anyone any idea what has changed in 2007?

Thanks

A

PARAMETERS [Forms]![frmStudentModules]![StudentModuleID] Long;
SELECT tblStudentModules.StudentModuleID, tblStudents.*, tblModule.Title AS
ModuleName, tblStudentModules.[Module Tutor], tblTutors.Email
FROM tblModule INNER JOIN ((tblTutors INNER JOIN tblStudents ON
tblTutors.TutorID = tblStudents.[Student Tutor]) INNER JOIN
tblStudentModules ON tblStudents.StudentID = tblStudentModules.Student) ON
tblModule.[Module Code] = tblStudentModules.Module
WHERE
(((tblStudentModules.StudentModuleID)=[Forms]![frmStudentModules]![StudentModuleID]));

Reply to this Message

Allen Browne - 19 Nov 2007 12:44 GMT

Just tested, and that kind of query works fine here in Access 2007.

Wonder what's different in your case?
This is not a passthrough query is it?
You haven't checked the SQL Server Compatible Syntax option under:
Office Button | Access Options | Object Designers?

Make sure you have unchecked the Name AutoCorrect buttons under:
Office Button | Access Options | Current Database
Then compact the database:
Office Button | Manage | Compact/Repair
Explanation of why:
http://allenbrowne.com/bug-03.html

If its still stuck, you might delete the query, and paste the SQL statement
into a new query.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi I am running the SQL query below in Access 2002/2003 without any
> issues, but in Access 2007 it comes up with a 3139 Syntax error in the
[quoted text clipped - 16 lines]
> WHERE
> (((tblStudentModules.StudentModuleID)=[Forms]![frmStudentModules]![StudentModuleID]));

Reply to this Message

alex - 20 Nov 2007 19:15 GMT

Hi Allen and thanks for your Post

i have checked the two items and no both are unchecked.

It's not actually a query, the SQL is actually the Record Source of a Report
definition. does that make sense?

Alex

> Just tested, and that kind of query works fine here in Access 2007.
>
[quoted text clipped - 34 lines]
>> WHERE
>> (((tblStudentModules.StudentModuleID)=[Forms]![frmStudentModules]![StudentModuleID]));

Reply to this Message

alex - 20 Nov 2007 19:17 GMT

Hi Allen

As an addition, if i try and run the report on its own, i get the Parameter
error message, and if I then click on help I get:
You entered an SQL statement with an invalid PARAMETERS declaration.

Possible causes:
A reserved word or argument name is misspelled or missing.
Punctuation is incorrect.

As far as I am aware its Ok isn't it?

Alex

Reply to this Message

Allen Browne - 20 Nov 2007 23:03 GMT

Try creating a new query, and pasting the SQL statement in. Then change the
RecordSource to the name of the new query.

It would be interesting to see if this process helps you identify where the
problem lies.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi Allen
>
[quoted text clipped - 48 lines]
>>> WHERE
>>> (((tblStudentModules.StudentModuleID)=[Forms]![frmStudentModules]![StudentModuleID]));

Reply to this Message

alex - 26 Nov 2007 20:00 GMT

Hi Allen

Thanks for your continued help

Very intersting - did as you suggested and that sorted the problem, the
report opens perfectly.

Alex

> Try creating a new query, and pasting the SQL statement in. Then change
> the RecordSource to the name of the new query.
[quoted text clipped - 54 lines]
>>>> WHERE
>>>> (((tblStudentModules.StudentModuleID)=[Forms]![frmStudentModules]![StudentModuleID]));

Reply to this Message