engine does not recognize " as a valid field name

  • Thread starter Thread starter Mary Fran
  • Start date Start date
M

Mary Fran

I have a report with the following query as its recordsource. It works fine
in Access 2003 but when I run in 2007 I get the "engine does not
recognize..." error. Can anyone help?
PARAMETERS [forms]![frmSelectTerm]![TermYear] Text ( 255 ),
[forms]![frmSelectTerm]![Term] Text ( 255 ),
[forms]![frmSelectTerm]![TermYear2] Text ( 255 ),
[forms]![frmSelectTerm]![Term2] Text ( 255 );
TRANSFORM sub.count
SELECT sub.Sport
FROM (SELECT Sub2.*, tblcCourses.Course, tblcCourses.CourseName,
tblcCourses.CourseCategory, Val(nz([count])) AS RecCount FROM (SELECT
DISTINCT 1 AS [count], tbllTutorsAthletesCourses.AthleteID,
tbllTutorsAthletesCourses.CourseID, tblmStudents.SSN,
tbllTutorsAthletesCourses.TutorID, tbllTutorsAthletesCourses.Year,
tblStudents.Sport, tblStudents.[Student ID], tblGPA.Term, tblGPA.Year,
tblGPA.Level, tblStudents.[Last Name], tblStudents.[First Name] FROM
((tblmStudents INNER JOIN tblStudents ON
tblmStudents.SSN=tblStudents.[Student ID]) INNER JOIN tblGPA ON
tblStudents.[Student ID]=tblGPA.StudentID) INNER JOIN
tbllTutorsAthletesCourses ON
tblmStudents.StudentID=tbllTutorsAthletesCourses.AthleteID WHERE
(((tbllTutorsAthletesCourses.Year)=2000+Val(forms!frmSelectTerm!TermYear))
And ((tblGPA.Term)=forms!frmSelectTerm!Term) And
((tblGPA.Year)=forms!frmSelectTerm!TermYear) And
((tbllTutorsAthletesCourses.Semester)=forms!frmSelectTerm!Term)) Or
(((tbllTutorsAthletesCourses.Year)=2001+Val(forms!frmSelectTerm!TermYear))
And ((tblGPA.Term)=forms!frmSelectTerm!Term2) And
((tblGPA.Year)=forms!frmSelectTerm!TermYear2) And
((tbllTutorsAthletesCourses.Semester)=forms!frmSelectTerm!Term2)) ORDER BY
tbllTutorsAthletesCourses.AthleteID, tblStudents.Sport, tblStudents.[Last
Name], tblStudents.[First Name]) AS sub2 RIGHT JOIN tblcCourses ON
Sub2.CourseID=tblcCourses.CourseID) AS sub
WHERE (((sub.Sport) Is Not Null))
GROUP BY sub.Sport
PIVOT sub.CourseCategory;
 
Yes, it just gives the "

Jeff Boyce said:
Mary

Are you saying that the error message doesn't provide the field name it
considers not valid? Is it only providing a zero-length string, or what?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Mary Fran said:
I have a report with the following query as its recordsource. It works fine
in Access 2003 but when I run in 2007 I get the "engine does not
recognize..." error. Can anyone help?
PARAMETERS [forms]![frmSelectTerm]![TermYear] Text ( 255 ),
[forms]![frmSelectTerm]![Term] Text ( 255 ),
[forms]![frmSelectTerm]![TermYear2] Text ( 255 ),
[forms]![frmSelectTerm]![Term2] Text ( 255 );
TRANSFORM sub.count
SELECT sub.Sport
FROM (SELECT Sub2.*, tblcCourses.Course, tblcCourses.CourseName,
tblcCourses.CourseCategory, Val(nz([count])) AS RecCount FROM (SELECT
DISTINCT 1 AS [count], tbllTutorsAthletesCourses.AthleteID,
tbllTutorsAthletesCourses.CourseID, tblmStudents.SSN,
tbllTutorsAthletesCourses.TutorID, tbllTutorsAthletesCourses.Year,
tblStudents.Sport, tblStudents.[Student ID], tblGPA.Term, tblGPA.Year,
tblGPA.Level, tblStudents.[Last Name], tblStudents.[First Name] FROM
((tblmStudents INNER JOIN tblStudents ON
tblmStudents.SSN=tblStudents.[Student ID]) INNER JOIN tblGPA ON
tblStudents.[Student ID]=tblGPA.StudentID) INNER JOIN
tbllTutorsAthletesCourses ON
tblmStudents.StudentID=tbllTutorsAthletesCourses.AthleteID WHERE
(((tbllTutorsAthletesCourses.Year)=2000+Val(forms!frmSelectTerm!TermYear))
And ((tblGPA.Term)=forms!frmSelectTerm!Term) And
((tblGPA.Year)=forms!frmSelectTerm!TermYear) And
((tbllTutorsAthletesCourses.Semester)=forms!frmSelectTerm!Term)) Or
(((tbllTutorsAthletesCourses.Year)=2001+Val(forms!frmSelectTerm!TermYear))
And ((tblGPA.Term)=forms!frmSelectTerm!Term2) And
((tblGPA.Year)=forms!frmSelectTerm!TermYear2) And
((tbllTutorsAthletesCourses.Semester)=forms!frmSelectTerm!Term2)) ORDER BY
tbllTutorsAthletesCourses.AthleteID, tblStudents.Sport, tblStudents.[Last
Name], tblStudents.[First Name]) AS sub2 RIGHT JOIN tblcCourses ON
Sub2.CourseID=tblcCourses.CourseID) AS sub
WHERE (((sub.Sport) Is Not Null))
GROUP BY sub.Sport
PIVOT sub.CourseCategory;
Click to expand...
Click to expand...
 
Is it being run from a trusted location?
Try cutting and pasting it into anouther query then renaming it back to the
original.
This worked for me although my error message was different.
 
I did what you suggested here and now get "does not recognize 'count' as a
valid field"

Mike Painter said:
Is it being run from a trusted location?
Try cutting and pasting it into anouther query then renaming it back to the
original.
This worked for me although my error message was different.

Mary said:
I have a report with the following query as its recordsource. It
works fine in Access 2003 but when I run in 2007 I get the "engine
does not recognize..." error. Can anyone help?
PARAMETERS [forms]![frmSelectTerm]![TermYear] Text ( 255 ),
[forms]![frmSelectTerm]![Term] Text ( 255 ),
[forms]![frmSelectTerm]![TermYear2] Text ( 255 ),
[forms]![frmSelectTerm]![Term2] Text ( 255 );
TRANSFORM sub.count
SELECT sub.Sport
FROM (SELECT Sub2.*, tblcCourses.Course, tblcCourses.CourseName,
tblcCourses.CourseCategory, Val(nz([count])) AS RecCount FROM (SELECT
DISTINCT 1 AS [count], tbllTutorsAthletesCourses.AthleteID,
tbllTutorsAthletesCourses.CourseID, tblmStudents.SSN,
tbllTutorsAthletesCourses.TutorID, tbllTutorsAthletesCourses.Year,
tblStudents.Sport, tblStudents.[Student ID], tblGPA.Term, tblGPA.Year,
tblGPA.Level, tblStudents.[Last Name], tblStudents.[First Name] FROM
((tblmStudents INNER JOIN tblStudents ON
tblmStudents.SSN=tblStudents.[Student ID]) INNER JOIN tblGPA ON
tblStudents.[Student ID]=tblGPA.StudentID) INNER JOIN
tbllTutorsAthletesCourses ON
tblmStudents.StudentID=tbllTutorsAthletesCourses.AthleteID WHERE
(((tbllTutorsAthletesCourses.Year)=2000+Val(forms!frmSelectTerm!TermYear))
And ((tblGPA.Term)=forms!frmSelectTerm!Term) And
((tblGPA.Year)=forms!frmSelectTerm!TermYear) And
((tbllTutorsAthletesCourses.Semester)=forms!frmSelectTerm!Term)) Or
(((tbllTutorsAthletesCourses.Year)=2001+Val(forms!frmSelectTerm!TermYear))
And ((tblGPA.Term)=forms!frmSelectTerm!Term2) And
((tblGPA.Year)=forms!frmSelectTerm!TermYear2) And
((tbllTutorsAthletesCourses.Semester)=forms!frmSelectTerm!Term2))
ORDER BY tbllTutorsAthletesCourses.AthleteID, tblStudents.Sport,
tblStudents.[Last Name], tblStudents.[First Name]) AS sub2 RIGHT
JOIN tblcCourses ON Sub2.CourseID=tblcCourses.CourseID) AS sub
WHERE (((sub.Sport) Is Not Null))
GROUP BY sub.Sport
PIVOT sub.CourseCategory;
Click to expand...
Click to expand...
 
Mary said:
I did what you suggested here and now get "does not recognize 'count'
as a valid field"
Click to expand...

Count is a reserved word and it is dangerouse to use them anyplace in
Access. 2007 tries to not allow them.
The problem is that they will sometimes work and sometimes won't.

Was it the cut and paste or trusted location?
Mike Painter said:
Is it being run from a trusted location?
Try cutting and pasting it into anouther query then renaming it back
to the original.
This worked for me although my error message was different.

Mary said:
I have a report with the following query as its recordsource. It
works fine in Access 2003 but when I run in 2007 I get the "engine
does not recognize..." error. Can anyone help?
PARAMETERS [forms]![frmSelectTerm]![TermYear] Text ( 255 ),
[forms]![frmSelectTerm]![Term] Text ( 255 ),
[forms]![frmSelectTerm]![TermYear2] Text ( 255 ),
[forms]![frmSelectTerm]![Term2] Text ( 255 );
TRANSFORM sub.count
SELECT sub.Sport
FROM (SELECT Sub2.*, tblcCourses.Course, tblcCourses.CourseName,
tblcCourses.CourseCategory, Val(nz([count])) AS RecCount FROM
(SELECT DISTINCT 1 AS [count], tbllTutorsAthletesCourses.AthleteID,
tbllTutorsAthletesCourses.CourseID, tblmStudents.SSN,
tbllTutorsAthletesCourses.TutorID, tbllTutorsAthletesCourses.Year,
tblStudents.Sport, tblStudents.[Student ID], tblGPA.Term,
tblGPA.Year, tblGPA.Level, tblStudents.[Last Name],
tblStudents.[First Name] FROM ((tblmStudents INNER JOIN tblStudents
ON tblmStudents.SSN=tblStudents.[Student ID]) INNER JOIN tblGPA ON
tblStudents.[Student ID]=tblGPA.StudentID) INNER JOIN
tbllTutorsAthletesCourses ON
tblmStudents.StudentID=tbllTutorsAthletesCourses.AthleteID WHERE
(((tbllTutorsAthletesCourses.Year)=2000+Val(forms!frmSelectTerm!TermYear))
And ((tblGPA.Term)=forms!frmSelectTerm!Term) And
((tblGPA.Year)=forms!frmSelectTerm!TermYear) And
((tbllTutorsAthletesCourses.Semester)=forms!frmSelectTerm!Term)) Or
(((tbllTutorsAthletesCourses.Year)=2001+Val(forms!frmSelectTerm!TermYear))
And ((tblGPA.Term)=forms!frmSelectTerm!Term2) And
((tblGPA.Year)=forms!frmSelectTerm!TermYear2) And
((tbllTutorsAthletesCourses.Semester)=forms!frmSelectTerm!Term2))
ORDER BY tbllTutorsAthletesCourses.AthleteID, tblStudents.Sport,
tblStudents.[Last Name], tblStudents.[First Name]) AS sub2 RIGHT
JOIN tblcCourses ON Sub2.CourseID=tblcCourses.CourseID) AS sub
WHERE (((sub.Sport) Is Not Null))
GROUP BY sub.Sport
PIVOT sub.CourseCategory;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top