IIF statement in SQL as row source in code

[kbdci]

I am trying to create a string for a SQL statement in code that gives the same results as the following built in a standard Access query:
SELECT tblContributor.ContributorKey, [tblContributor]![LastName] & IIf(IsNull([tblContributor]![Suffix]),"",(", " & [tblContributor]![Suffix])) & ", " & IIf(IsNull([tblContributor]![Prefix]),"",([tblContributor]![Prefix] & " &quot) & [tblContributor]![FirstName] AS Name, tblContributor.LastName
FROM tblContributor
ORDER BY [tblContributor]![LastName] & IIf(IsNull([tblContributor]![Suffix]),"",(", " & [tblContributor]![Suffix])) & ", " & IIf(IsNull([tblContributor]![Prefix]),"",([tblContributor]![Prefix] & " &quot) & [tblContributor]![FirstName];

When I paste this query into code to be used as the basis for a combo box row source, I get a compile error “Expected end of statement” with the first comma in the first IIf statement ((", " ) highlighted.

Can I use a statement like this to set the row source in code? What changes are needed to do that?

Thanks.

 

[dynamictiger]

Never tried it but have you considered using Nz instead of iif.

Nz([tblContributor]![Suffix])

 

[1skywalker1]

Did you ever get an answer to this question?

I'm trying to do the same thing:

IIf(IsNull([Account].[LastName]),[Account].[Company],[Account].[Lastname] & ", " & IIf(IsNull([Spouse].[FirstName]),[Account].[FirstName],[Account].[FirstName] & " and " & [Spouse].[FirstName])

Works great in Access, but how can I do this in SQL? I know that the IsNull function is different as well, but I can get around that by using IsNull([Account].[LastName],FLASE) assuming IIF worked.

Please let me know if you have a solution for this. Thanks!