Helping Converting Access Query into SQL View


I have the following on a field value for an Access query and need to know how it is written in SQL as an expression?

IIf([Surname2]=[Surname1],[Title1] & " & " & [Title2] & " " & [Surname1],[Title1] & " " & [Surname1] & IIf(IsNull([Surname2]),""," & " & [Title2] & " " & [Surname2]))
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
    WHEN Surname2=Surname1 THEN Title1 + ' & ' + Title2 + ' ' + Surname1
    ELSE Title1 + ' ' + Surname1 + CASE 
                                     WHEN Surname2 IS NULL THEN ''
                                     ELSE ' & ' + Title2 + ' ' + Surname2
                                END CASE

Open in new window

ste5anSenior DeveloperCommented:
CAVEAT: The ampersand operator & in Access (VBA) handles NULL differently then the T-SQL operator plus +.

Access: NULL & "SomeText" = > "SomeText"
T-SQL:  NULL + "SomeText" => NULL

So it depends on the nullability of the involved columns. You need for each column which allows to be NULL:  IsNull([ColumnName], '')

For example:

CASE WHEN Surname2 = Surname1 
	THEN ISNULL(Title1, '') + ' & ' + ISNULL(Title2, '') + ' ' + Surname1
	ELSE ISNULL(Title1, '') + ' ' + Surname1 + ISNULL(' & ' + ISNULL(Title2, '') + ' ' + Surname2, '')

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Data DudeCommented:
Looks like both experts are correct.

If you'd like some extra reading, Migrating your Access Queries to SQL Server Transact-SQL covers basic Access to SQL query conversions, and SQL Server CASE Solutions covers CASE.  

@ste5an - Good stuff, I didn't have that in my Access to SQL article.  Mind if I update it what your comment and give you the credit?
ste5anSenior DeveloperCommented:
@Jim: Sure. Also notice that the plus + in Access works the same as in T-SQL.

So a typical Access expression could be: LastName & ", " + FirstName
In T-SQL: LastName + ISNULL(', ' + FirstName, '')

(LastName not null, FirstName allows null).
Jim HornMicrosoft SQL Server Data DudeCommented:
Done, Migrating your Access Queries to SQL Server Transact-SQL, added the String Concatenation section 1/2 way down, plus did a wompload of cosmetic cleanup as the article authoring tool has changed a lot in the last two years.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.