By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,491 Members | 879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,491 IT Pros & Developers. It's quick & easy.

adding middle initial field

100+
P: 180
I want to add the middle initial field in contacts extended query.
Expand|Select|Wrap|Line Numbers
  1. 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
Jan 12 '12 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. 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 :
Expand|Select|Wrap|Line Numbers
  1. Contact Name: ([First Name] + ' ') & ([Middle Initial] & ' ') & [Last Name]

Share this Question
Share on Google+
8 Replies


100+
P: 180
it works, I only add after [first name]&" "&[Middle Initial]&" "&[last Name]
Expand|Select|Wrap|Line Numbers
  1. Contact Name: IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] &" " & [Middle Initial] & " "  & [Last Name]))
Jan 12 '12 #2

Expert 100+
P: 446
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 ;-

Expand|Select|Wrap|Line Numbers
  1.  .....
  2. ,[first name]& IIF(IsNull[Middle Initial]," "," " & [Middle Initial] & ". ") & [Last Name].....
  3.  
S7
Jan 12 '12 #3

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. Contact Name: ([First Name] + ' ') & ([Middle Initial] & '. ') & [Last Name]
See Using "&" and "+" in WHERE Clause for more on how this works.
Jan 12 '12 #4

100+
P: 180
Thanks sierra7, thanks neopa. sorry again, i thought it doesn't need to be tag.
Jan 13 '12 #5

100+
P: 180
it became double period the middle initial after i used your code neopa

Expand|Select|Wrap|Line Numbers
  1. Contact Name: ([First Name] + ' ') & ([Middle Initial] & '. ') & [Last Name]
it became like this: Juan C.. Dela Cruz
Jan 13 '12 #6

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. 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 :
Expand|Select|Wrap|Line Numbers
  1. Contact Name: ([First Name] + ' ') & ([Middle Initial] & ' ') & [Last Name]
Jan 13 '12 #7

100+
P: 180
Thanks Neopa, i fixed my data not to include period.
Jan 16 '12 #8

NeoPa
Expert Mod 15k+
P: 31,186
That's very sensible :-) You seem to be learning since you've been using Bytes.
Jan 16 '12 #9

Post your reply

Sign in to post your reply or Sign up for a free account.