adding middle initial field

[eneyardi]

I want to add the middle initial field in contacts extended query.

File As: IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name]))

where can i put [middle initial] so that when i view the report the complete name will appear. ex. Juan c. Dela Cruz

[eneyardi]

it works, I only add after [first name]&" "&[Middle Initial]&" "&[last Name]

Contact Name: IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[B][First Name] &" " & [Middle Initial] & " "  & [Last Name][/B]))

[sierra7]

Hi,
You seem to have fixed this to your satisfaction but you will always have two spaces between first and lastname.

In your example you had a fullstop (dot, punto, período) after the middle initial, so you may want to do something like ;-

 .....
,[first name]& IIF(IsNull[Middle Initial]," "," " & [Middle Initial] & ". ") & [Last Name].....

S7

[NeoPa]

Firstly, Please read the PM I sent carefully. It may be your final warning. Any discussion of such must be via PM.

As for your question, I will ignore the [Company] field as the logic for that isn't explained anywhere, but assuming you want to display the optional [First Name], followed by the optional [Middle Initial] followed by the mandatory [Last Name], you could try :

Contact Name: ([First Name] + ' ') & ([Middle Initial] & '. ') & [Last Name]

See Using "&" and "+" in WHERE Clause for more on how this works.

[eneyardi]

Thanks sierra7, thanks neopa. sorry again, i thought it doesn't need to be tag.

[eneyardi]

it became double period the middle initial after i used your code neopa

Contact Name: ([First Name] + ' ') & ([Middle Initial] & '. ') & [Last Name]

it became like this: Juan C.. Dela Cruz

[NeoPa]

eneyardi:
it became like this: Juan C.. Dela Cruz

That tells me that your [Middle Initial] field has been populated with (includes) the period. This is not good design. The period is not (should not be) part of the data.

NeoPa:
Assuming you want to display the optional [First Name], followed by the optional [Middle Initial] followed by the mandatory [Last Name], you could try :

Contact Name: ([First Name] + ' ') & ([Middle Initial] & '. ') & [Last Name]

See Using "&" and "+" in WHERE Clause for more on how this works.

The best idea is to fix your data. However, while the data is still broken simply use the following instead :

Contact Name: ([First Name] + ' ') & ([Middle Initial] & ' ') & [Last Name]

[eneyardi]

Thanks Neopa, i fixed my data not to include period.

[NeoPa]

That's very sensible :-) You seem to be learning since you've been using Bytes.