Multiple IIF in Query for Report

[Guest]

Dear Professionals:

In my table my fields are:

Salutation
FirstName
Middle Name
Last Name

In my report I have one field in which I want to concatenate the full name.
I only need to list salutation if the saluation is Dr. and in some cases I
have a middle initial or name and in some I do not.

Here is my current IIF statement for the field in my report:
=IIf([Salutation]="Dr.",[Salutation] & " " & [FirstName] & " " &
[LastName],[FirstName] & " " & [LastName])

I have totally excluded MiddleName from the statement because I could not
figure out how to account for each of the situations below and keep the
spacing correct:

Dr. with first, middle, last
Dr. with first, last name
No Dr. with first, last, middle
No Dr. with first, last

Thank you in advance.

 

[Guest]

I'm not sure if that what you are looking for

=IIf([Salutation]="Dr.",[Salutation] & " " & [FirstName] & " " & ([Middle
Name] + " ") & [LastName],[FirstName] & " " & ([LastName] + " ") & [Middle
Name])

 

[John Spencer]

= IIF(Salutation="Dr.","Dr. ") & [First Name] & (" " + [Middle Name]) & (" " +
[Last Name])

This takes into account the different ways that & or + work when adding together strings.

If a value is null, then the + will add that null to the string and give you
NULL. So " " + Middle Name will end up being blank, while " " & Middle Name
will end up being one space long.