Access Query to SQL

[joycesolomon]

Hi

I am new to Microsoft SQL server database. Can someone please coach me how to conver the below Access query to SQL

SELECT IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])) AS [File As], IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name])) AS [Contact Name], Contacts.*
FROM Contacts
ORDER BY IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])), IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name]));


Thank you

Joyce

 

[Andrzejek]

Try SQL server case statement


Have fun.

---- Andy

 

[joycesolomon]

can you please give me an example of a case statement?

eg: IIf([Status]<>"Completed" And Int([Due Date])<Int(Date)


is it right to say:

CASE WHEN Status <> 'Completed' End And (CONVERT(varchar, [Due Date], 101) < GETDATE())

 

[dhookom]

You can use IsNull() in SQL Server like you would Nz() in Access. For instance:
IsNull(LastName, CompanyName)
will return CompanyName if LastName is Null.

CASE WHEN [true/false expression here] THEN [True Here] ELSE [False Here] END

Duane
Hook'D on Access
MS Access MVP