shivendra

shivendra

  • NA
  • 59
  • 0

How To Covert MS Access Queries To MS SQL

Aug 11 2005 6:56 AM
  Hello All
    I have A Situation Where I have To Convert Access To MS Sql. This

SELECT Job.*, IIf(IsNull([Staff_Site].[SiteAltSiteID]),[Staff_Site].[SiteName],[AltStaffSite].[SiteName]) AS StaffSiteName,
 IIf(IsNull([Site_List].[SiteAltSiteID]),[Site_List].[SiteName],[AltJobSite].[SiteName]) AS JobSiteName, AltJobSite.SiteAltSiteID,
ruGetAdEmail([JPEmailType],Nz([Staff_Site].[SiteHireEmail]),Nz([StaffEmail])) AS AdEmail, "Send resumes and cover letters"
& IIf(IsNull([JPAppDueDate]),""," by " & [JPAppDueDate]) & " to: " & [StaffFirstName] & " " & [StaffLastName] & ", "
 & ruSiteNamePhrase([StaffSiteName]) & ", " & [Staff_Site].[SiteAddress] & [Staff_Site].[SiteAddress2] & ", "
 & [Staff_Site].[SiteCity] & ", " & [Staff_Site].[SiteState] & "  " & [Staff_Site].[SiteZip] & ".  " & IIf([JPContactMethod]="Fax"
Or "Fax & Email",IIf(IsNull([StaffFax]),"","Fax: " & [StaffFax]),"") & IIf([JPContactMethod]="Fax & Email",IIf(IsNull([AdEmail]),"",",
 Email:" & [AdEmail]),"") & IIf([JPContactMethod]="Mail Only","",".") AS BContactPhrase, "Send resumes and cover letters" &
 IIf(IsNull([JPAppDueDate]),""," by " & [JPAppDueDate]) & " to: " AS SContactPhrase1, Chr(13) & Chr(10) & [StaffFirstName] & " "
& [StaffLastName] & Chr(13) & Chr(10) & ruSiteNamePhrase([StaffSiteName]) & Chr(13) & Chr(10) & [Staff_Site].[SiteAddress] & [Staff_Site].[SiteAddress2] &
 Chr(13) & Chr(10) & [Staff_Site].[SiteCity] & ", " & [Staff_Site].[SiteState] & "  " & [Staff_Site].[SiteZip] & Chr(13) & Chr(10) & IIf([JPContactMethod]="Fax" Or
"Fax & Email",IIf(IsNull([StaffFax]),"","Fax: " & [StaffFax]),"") & Chr(13) & Chr(10) & IIf([JPContactMethod]="Fax & Email",IIf(IsNull([AdEmail]),""," " & [AdEmail]),"")
 AS SContactPhrase2, IIf(fuRepostTextExists([JobPostID]),[JPIntroStmntRP],[JPIntroStmnt]) AS IntroStmnt, IIf(fuRepostTextExists([JobPostID]),[JPDescriptionRP],
[JPDescription]) AS Description, IIf(fuRepostTextExists([JobPostID]),[JPResponsibilitiesRP],[JPResponsibilities]) AS Responsibilities, IIf(fuRepostTextExists([JobPostID]),
[JPRequirementsRP],[JPRequirements]) AS Requirements, IIf(fuRepostTextExists([JobPostID]),[JPSpecialCmmntRP],[JPSpecialCmmnt]) AS SpecialCmmnt
FROM (Site_List AS AltJobSite RIGHT JOIN ((CUCSStaff INNER JOIN (Site_List INNER JOIN Job ON Site_List.SiteID = Job.JPSiteID) ON CUCSStaff.StaffID =
Job.JPStaffID) INNER JOIN Site_List AS Staff_Site ON CUCSStaff.StaffSiteID = Staff_Site.SiteID) ON AltJobSite.SiteID = Site_List.SiteAltSiteID) LEFT
JOIN Site_List AS AltStaffSite ON Staff_Site.SiteAltSiteID = AltStaffSite.SiteID;