IIF & IsNull Functions

Below in "ACCESS SQL CODE" is a query that I use in Access. It uses the IIf
and IsNull functions to concatenate the full name. If there is a middle
initial, my statement returns a comma after the LastName field, the middle
initial followed by a period. If it doesn't exist, sql returns nothing.
I'm upsizing the access database to an access adp project file and
converting my queries into views. When I try to run the below "SQL VIEW
CODE", I get an error saying "The isnull function requires 2 arguments".
I thought my syntax was right. What am I doing wrong? Is the IIf function
causing the problem in my Access ADP view?
ACCESS SQL CODE *************
SELECT LastName, FirstName, MiddleInit, LastName & ", " & FirstName &
IIf(IsNull(MiddleInit),""," " & MiddleInit & ".") AS Name
FROM myTable
SQL VIEW CODE ****************
SELECT FirstName, t_Users.MiddleInit,
LastName + ", " + FirstName + IIf(IsNull(MiddleInit),'',' ' + MiddleInit &
'.') AS Name
FROM myTableOn Wed, 26 Apr 2006 16:54:07 -0500, scott wrote:

>SELECT FirstName, t_Users.MiddleInit,
>LastName + ", " + FirstName + IIf(IsNull(MiddleInit),'',' ' + MiddleInit &
>'.') AS Name
>FROM myTable
Hi Scott,
IIf and IsNull are Access-specific functions that won't work in SQL
Server or in any ANSI-compliant relational database.
SQL Server also has an ISNULL function; it's function is roughly the
same as Access' Nz function. And it''s allso non-ANSI-compliant.
The ANSI-compliant version of yoour query (that will work on SQL Server
and other ANNSI-compliant databases, but not on Access) is
SELECT FirstName, t_Users.MiddleInit,
LastName + ', ' + FirstName + COALESCE(' ' + MiddleInit + '.'),
'') AS Name
FROM myTable
Hugo Kornelis, SQL Server MVP|||thanks.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:5ssv42tvikim4oi8cnl3b6taln8bq76hhf@.
4ax.com...
> On Wed, 26 Apr 2006 16:54:07 -0500, scott wrote:
>
> Hi Scott,
> IIf and IsNull are Access-specific functions that won't work in SQL
> Server or in any ANSI-compliant relational database.
> SQL Server also has an ISNULL function; it's function is roughly the
> same as Access' Nz function. And it''s allso non-ANSI-compliant.
> The ANSI-compliant version of yoour query (that will work on SQL Server
> and other ANNSI-compliant databases, but not on Access) is
> SELECT FirstName, t_Users.MiddleInit,
> LastName + ', ' + FirstName + COALESCE(' ' + MiddleInit + '.'),
> '') AS Name
> FROM myTable
> --
> Hugo Kornelis, SQL Server MVP