If I may jump in here, I have spent ages converting Access SQL to the T-SQL used by MS SQL Server and I feel your pain Lstclair! As WendellB says, the big thing is to use CASE statements instead of IIF, but there are a load of common conversion issues illustrated by your question. So here goes with a long answer.
Your first expression is
And that can be put into a SQL view like this:
That still won't work though, because T-SQL has no Format() function. See later.
The above illustrates several things:
1. The syntax for creating a view. Note that once created the view lives in the database like an Access query. If you want to change it later, replace CREATE with ALTER.
2. T-SQL uses double quotes not square brackets to "escape" identifiers. You only need to do that if your identifiers have spaces or other characters T-SQL doesn't allow. As yours don't, you could omit the double quotes and the SET QUOTED_IDENTIFIER.
3. If QUOTED_IDENTIFIER is ON, you must use single quotes for string literals. That is good practice anyway and it also works in Access. Note you can put double quotes inside a single quoted string literal without escaping them, but if you include an apostrophe you must escape it by using two apostrophes.
4. +, not &, is the string concatenation operator in T-SQL
5. Not illustrated above is the ISNULL problem you would hit in your second expression. In T-SQL there is a function ISNULL() but it actually replaces a NULL value with some other value, like Access NZ(). To get the effect of Access SQL IIF(IsNull(field),expression1, expression2) you need something like
Also, if you need to qualify a field name with a table name use a dot, like table1.field1 or, if you need to quote it, "table 1"."field 1".
(digression: I think it really pays to make field names unique, by adopting a convention of using short table-name prefixes on field names, like tblAddresses.adrStreet. That "adr"is faster to type and much shorter than "tblAddresses." but still allows you (and SQL) to work out which table contains the field. When displaying results you can put "user friendly" names in the front-end form)
Now the Format(value, codes) problem.
To quote someone else on the subject "why would you want to format text? T-SQL is for storage, use a front-end for formatting". That quote isn't the whole story, because I use the database for formatting all the time when using MySQL, which has a plethora of formatting functions to help me do that, but it does seem to be the T-SQL philosophy.
T-SQL has a function CONVERT which changes datatypes and has limited string-formatting ability. You could use something like this:
You wouldn't want to type that very often, so you could write a user defined function in T-SQL to do it
What the above does incidentally is to convert numFloat, which is a float type, to a money type, then convert that to a varchar string using format 1 which is like 1,234.56 and finally strip off the .56 using substring. If your money values are already stored as money types you have an easier time of it. There may be an easier way to do it, I always format in the front end when using MS SQL.
And finally (!) a big disclaimer. In the above examples I've skipped over issues around schemas. I know that when I tested the "create view" with the FormatMoney function T-SQL insisted I use dbo.FormatMoney, there may be other issues in your setup.
Hope I have helped, not confused, you