engine does not recognize " as a valid field name

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;
 
M

Mary Fran

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...
 
M

Mike Painter

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.
 
M

Mary Fran

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...
 
M

Mike Painter

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

database engine does not recognize as valid field name 1
engine does not recognize <name> as a valid field name or expression.(Error 3070) 3
The Microsoft Jet database engine does not recognize <name> as a 7
Engine does not recognize " as a valid field 5
Micorsoft Jet does not recognize forms!form1!Calendar0 as a valid feild name or expression 2
Micorsoft Jet does not recognize forms!form1!Calendar0 as a valid feild name or expression 1
MS Jet DB engine does not recognize 'my query name' as a valid name 7
Access Does not recognize " as a valid field name or expression 1

Top