SQL Query in 2007

[Alexey]

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]));

 

[Allen Browne]

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.

 

[alex]

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.

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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
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]));

 

[alex]

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

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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
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]));

 

[Allen Browne]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

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]));

 

[alex]

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.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

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]));