error in recordsource after converting to sql server 2000

[jaYPee]

I have converted access 97 to access xp 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"

now this is my recordsource in my report.

SELECT Students.IDNo, [LastName] & ", " & [FirstName] & " " &
[MiddleName] AS Name, Students.Address, Students.PlaceofBirth,
Students.DateofBirth, Students.[Parent/Guardian], 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]));

i would be very glad if someone can help me on this matter.

thanks in advance

[Erland Sommarskog]

jaYPee (hi******@yahoo.com) writes:

I have converted access 97 to access xp 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"

now this is my recordsource in my report.

SELECT Students.IDNo, [LastName] & ", " & [FirstName] & " " &
[MiddleName] AS Name, Students.Address, Students.PlaceofBirth,
Students.DateofBirth, Students.[Parent/Guardian], 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]));

i would be very glad if someone can help me on this matter.

I don't know about Access, so I don't know whether this code runs in
Access or on SQL Server. But in case of the latter, be aware of that
although both runs SQL, there are vast differences in he SQL dialects.
For instance, in the above you use IIf() which is Access-specific. In
SQL Server you use the CASE epxression instead.

However, the error message does not come from SQL Server, so maybe you
are trying to execute in Access after all, but the tables are in SQL
Server.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Trevor Best]

jaYPee wrote:

I have converted access 97 to access xp 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"

now this is my recordsource in my report.

SELECT Students.IDNo, [LastName] & ", " & [FirstName] & " " &
[MiddleName] AS Name, Students.Address, Students.PlaceofBirth,
Students.DateofBirth, Students.[Parent/Guardian], 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]));

i would be very glad if someone can help me on this matter.

thanks in advance

I assume you went from an Access app to an Access ADP? As Erland says,
SQL Server doesn't support IIf() and the dialect is different so:

Select blah, blahblah,
Case [ProgramTitle]
When 'BEED' then 'with concentration in ' + [Major]
else [Major])
end AS MajorTemp, blahblahblah from...

or
Select blah, blahblah,
MajorTemp = Case [ProgramTitle]
When 'BEED' then 'with concentration in ' + [Major]
else [Major])
end , blahblahblah from...

Note the use of single quotes, SQL Server uses double quotes for column
names like Access uses [] (although it uses [] as well). I haven't used
ADPs myself so I don't know what Access will do with your forms!
parameter, whether it will evaluate it before sending the SQL to the
server or send it as is, if the latter it will fail as the server will
not know what's on your forms. Perhaps someone more experienced in ADPs
can throw some light on the subject.

Oh, also note that string concatenation is acheived using the + operator
and not the & and that it works more like Access when using + instead of
& as well in that concatenating a string to a null will give a null
result. Also note on that point that implicit conversion does not take
place on the server either so if you want to concatenate a string to an
integer you will have to convert it explicitly, e.g.

[MyString] + Cast([MyInt] As Varchar) or
[MyString] + Convert(varchar,[MyInt])

JIC [MyString] was null, you'd use:
ISNULL([MyString],'') + Cast([MyInt] As Varchar)

ISNULL() on SQL Server is the equivalent of Nz() in Access.
--
Error reading sig - A)bort R)etry I)nfluence with large hammer

[jaYPee]

Thanks for the reply. i have fix some errors except on how can i pass
my criteria "Forms]![Transcript of Records Dialog]![txtName]" to sql
server 2000.

i would be very glad if someone can teach me on how to pass this
parameter.
On Tue, 27 Apr 2004 00:16:44 +0100, Trevor Best <nospam@localhost>
wrote:

jaYPee wrote:

I have converted access 97 to access xp 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"

now this is my recordsource in my report.

SELECT Students.IDNo, [LastName] & ", " & [FirstName] & " " &
[MiddleName] AS Name, Students.Address, Students.PlaceofBirth,
Students.DateofBirth, Students.[Parent/Guardian], 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]));

i would be very glad if someone can help me on this matter.

thanks in advance

I assume you went from an Access app to an Access ADP? As Erland says,
SQL Server doesn't support IIf() and the dialect is different so:

Select blah, blahblah,
Case [ProgramTitle]
When 'BEED' then 'with concentration in ' + [Major]
else [Major])
end AS MajorTemp, blahblahblah from...

or
Select blah, blahblah,
MajorTemp = Case [ProgramTitle]
When 'BEED' then 'with concentration in ' + [Major]
else [Major])
end , blahblahblah from...

Note the use of single quotes, SQL Server uses double quotes for column
names like Access uses [] (although it uses [] as well). I haven't used
ADPs myself so I don't know what Access will do with your forms!
parameter, whether it will evaluate it before sending the SQL to the
server or send it as is, if the latter it will fail as the server will
not know what's on your forms. Perhaps someone more experienced in ADPs
can throw some light on the subject.

Oh, also note that string concatenation is acheived using the + operator
and not the & and that it works more like Access when using + instead of
& as well in that concatenating a string to a null will give a null
result. Also note on that point that implicit conversion does not take
place on the server either so if you want to concatenate a string to an
integer you will have to convert it explicitly, e.g.

[MyString] + Cast([MyInt] As Varchar) or
[MyString] + Convert(varchar,[MyInt])

JIC [MyString] was null, you'd use:
ISNULL([MyString],'') + Cast([MyInt] As Varchar)

ISNULL() on SQL Server is the equivalent of Nz() in Access.