By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,687 Members | 1,806 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,687 IT Pros & Developers. It's quick & easy.

convert calculated field to ms sql server 2000

P: n/a
Anyone know how can I convert this field to ms sql server?

IIf([Final]=0 Or IsNull([Final]),"--",[Final]) AS FinalGrade,
SchYrSemCourseJoin.Final, IIf([Final]=0 Or
IsNull([Final]),"--",[Unit]) AS UnitAlias
this is the full sql statement in my ms access program:

SELECT DISTINCTROW 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, Students.TerSchool,
Students.TSAddress, Students.TSSchYr, Program.ProgramTitle,
Program.ProgramDesc, IIf([ProgramTitle]="BEED","with concentration in
" & [Major],[Major]) AS MajorTemp, [Sem] & " Semester, " & [SchYr] AS
SchYrSem, Course.CourseCode, Course.CourseTitle, IIf([Final]=0 Or
IsNull([Final]),"--",[Final]) AS FinalGrade, SchYrSemCourseJoin.Final,
IIf([Final]=0 Or IsNull([Final]),"--",[Unit]) AS UnitAlias,
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

the only problem i have encounted is the FinalGrade and UnitAlias
Field as stated above.

Thanks in advance for any help.
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
> Anyone know how can I convert this field to ms sql server?

IIf([Final]=0 Or IsNull([Final]),"--",[Final]) AS FinalGrade,
SchYrSemCourseJoin.Final, IIf([Final]=0 Or
IsNull([Final]),"--",[Unit]) AS UnitAlias

You could try something like

SELECT .....

FinalGrade =
(CASE [Final]
WHEN 0 THEN '--'
WHEN NULL THEN '--'
ELSE [Final]
END) ,
SchYrSemCourseJoin.Final,
UnitAlias =
(CASE [Final]
WHEN 0 THEN '--'
WHEN NULL THEN '--'
ELSE [Unit]
END)

......FROM

Cheers,
Peter

Nov 13 '05 #2

P: n/a
Thank you.

On Thu, 17 Feb 2005 09:09:07 GMT, "Peter Hoyle"
<pe*********@ntlworld-NOT.com> wrote:
Anyone know how can I convert this field to ms sql server?

IIf([Final]=0 Or IsNull([Final]),"--",[Final]) AS FinalGrade,
SchYrSemCourseJoin.Final, IIf([Final]=0 Or
IsNull([Final]),"--",[Unit]) AS UnitAlias

You could try something like

SELECT .....

FinalGrade =
(CASE [Final]
WHEN 0 THEN '--'
WHEN NULL THEN '--'
ELSE [Final]
END) ,
SchYrSemCourseJoin.Final,
UnitAlias =
(CASE [Final]
WHEN 0 THEN '--'
WHEN NULL THEN '--'
ELSE [Unit]
END)

.....FROM

Cheers,
Peter


Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.