error in sql statement after converting access97 to access xp adp project

  • Thread starter Thread starter jaYPee
  • Start date Start date
J

jaYPee

I have converted access 97 to access xp adp project and connect the
table from my sql server 2000. my problem is that when i run my report
it says "Invalid SQL Statement. Check the server filter on the form
recordsource"

here's what the error says:

Error in list of function arguments: '=' not recognized.
Error in WHERE clause near '!'.
Unable to parse query text.

here's my sql statement:

SELECT Students.IDNo, [LastName] + ', ' + [FirstName] + ' ' +
[MiddleName] AS Name, Students.Address, Students.PlaceofBirth,
Students.DateofBirth,
Students.[ParentGuardian], Students.ElemSchool,
Students.ESAddress, Students.ESSchYear, Students.SecSchool,
Students.SSAddress,
Students.SSSchYear, Program.ProgramTitle,
Program.ProgramDesc, IIf([ProgramTitle] = 'BEED', 'with concentration
in ' + [Major], [Major])
AS MajorTemp, [Sem] + ' Semester, ' + [SchYr] AS
SchYrSem, Course.CourseCode, Course.CourseTitle,
SchYrSemCourseJoin.Final, Course.Unit,
Students.IDNo, SchYrSem.SchYrSemID,
SchYrSemCourseJoin.Annotation
FROM (Program INNER JOIN
Students ON Program.ProgramID =
Students.ProgramID) INNER JOIN
((Major INNER JOIN
SchYrSem ON Major.MajorID = SchYrSem.MajorID)
INNER JOIN
(Course INNER JOIN
SchYrSemCourseJoin ON Course.CourseID =
SchYrSemCourseJoin.CourseID) ON SchYrSem.SchYrSemID =
SchYrSemCourseJoin.SchYrSemID) ON
Students.IDNo = SchYrSem.IDNo
WHERE ((([LastName] + ', ' + [FirstName] + ' ' + [MiddleName]) =
[Forms] ! [Transcript of Records Dialog] ! [txtName]));

thanks in advance for any help.
 
Witaj jaYPee,
W Twoim liœcie datowanym 26 kwietnia 2004 (16:40:51) mo¿na przeczytaæ:

j> I have converted access 97 to access xp adp project and connect the
j> table from my sql server 2000. my problem is that when i run my report
j> it says "Invalid SQL Statement. Check the server filter on the form
j> recordsource"

j> here's what the error says:

j> Error in list of function arguments: '=' not recognized.
j> Error in WHERE clause near '!'.
j> Unable to parse query text.

My question:
Where did you put this code?

j> here's my sql statement:

j> Program.ProgramDesc, IIf([ProgramTitle] = 'BEED', 'with concentration
j> in ' + [Major], [Major])

There is no function IIF in SQL Server

j> [Forms] ! [Transcript of Records Dialog] ! [txtName]));

You cannot use this syntax in StoredProcedure/View (generally in
SqlServer) - its "AccessSQl" syntax, not T-SQL

Regards
Jacek Segit
 
(continue)

j>> IIf([ProgramTitle] = 'BEED', 'with concentration in ' + [Major], [Major])

select Case When ProgramTitle='BEED' then 'with concentration in ' + [Major]
Else [Major]
End, [OtherField] from ....

j>> [Forms] ! [Transcript of Records Dialog] ! [txtName]));

JS> You cannot use this syntax in StoredProcedure/View (generally in
JS> SqlServer) - its "AccessSQl" syntax, not T-SQL

You should use parameters:

/* SQl Code - START */
CREATE PROCEDURE MyFirstProcedure
@myfirstparameter varchar(255)
AS
SELECT MyTable.* FROM MyTable WHERE SomeField=@myfirstparameter
return
/* SQl Code - END */

If its source of form/report you should put into RecordSource field
of this particular form/report name of procedure:
dbo.MyFirstProcedure
and into InputParameters field:
@myfirstparameter varchar(255)=[Forms]![Transcript of Records Dialog]![txtName]

Regards
Jacek Segit
 
Back
Top