engine does not recognize " as a valid field name Mary Fran 12/5/2008 1:53:19 PM 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;

Re: engine does not recognize " as a valid field name "Jeff Boyce" <JeffBoyce_IF[ at ]msn.com-DISCARD_HYPHEN_TO_END> 12/5/2008 1:57:54 PM 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 http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Mary Fran" <MaryFran[ at ]discussions.microsoft.com> wrote in message news:3706A602-5E49-4B33-AB27-F18A2954D3D5[ at ]microsoft.com... [Quoted Text] > 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;

Re: engine does not recognize " as a valid field name Mary Fran 12/5/2008 2:22:23 PM Yes, it just gives the " "Jeff Boyce" wrote: [Quoted Text] > 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 > http://mvp.support.microsoft.com/ > > Microsoft IT Academy Program Mentor > http://microsoftitacademy.com/ > > "Mary Fran" <MaryFran[ at ]discussions.microsoft.com> wrote in message > news:3706A602-5E49-4B33-AB27-F18A2954D3D5[ at ]microsoft.com... > > 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; > >

Re: engine does not recognize " as a valid field name "Mike Painter" <mddotpainter[ at ]sbcglobal.net> 12/5/2008 2:42:34 PM 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 Fran wrote: [Quoted Text] > 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;

Re: engine does not recognize " as a valid field name Mary Fran 12/5/2008 2:55:11 PM I did what you suggested here and now get "does not recognize 'count' as a valid field" "Mike Painter" wrote: [Quoted Text] > 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 Fran wrote: > > 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; > > >

Re: engine does not recognize " as a valid field name "Mike Painter" <mddotpainter[ at ]sbcglobal.net> 12/5/2008 5:33:41 PM Mary Fran wrote: [Quoted Text] > I did what you suggested here and now get "does not recognize 'count' > as a valid field" 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" wrote: > >> 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 Fran wrote: >>> 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;