Place a period after a middle initial, if it is present.

I am now finishing up an application that I had a question about last week and have three main questions that I will post as separate questions.  Here is the first.

OK, I know I could just leave the period after the initial in my table and be done with this one, but I think there has to be a more artful way to manage it.

I am storing names in four distinct fields - first, mi, last, suffix.  When generating reports with the complete name, I want to be able to insert a period and a space (". ") after the middle initial.  The only reason it is complex is because only about 55% of my records have middle initials, so it needs to be conditional.

Any suggestions would be greatl appreciated.
mattturleyAsked:
Who is Participating?
 
Emanon_ConsultingConnect With a Mentor Commented:
So I am not sure where your "." is coming from but we can try to take care of it in the SQL like this

SELECT tblClassName.ID, IIf([prefix] Is Null Or [prefix]=".",[First] & " " & IIf([Mi] Is Null,[Last],[Mi] & ". " & [Last]),[prefix] & ". " & [First] & " " & IIf([Mi] Is Null,[Last],[Mi] & ". " & [Last])) AS FullName
FROM tblClassName;


This is the part we add
Or [prefix]="."

You might need to play with it a bit as in change the "." to " ." or ". " which is just adding a space before or after the "."

Technically Access should NOT be adding a " " (space) after anything in the table... so you should only have to test for "." OR " ."


Give that a try and let me know how it works...

Cheers
Michael
0
 
mattturleyAuthor Commented:
oops, this is the second question.
0
 
phileocaCommented:
in your report query
NewMi: [Table]![mi] & "."
in your report refer to NewMi
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
phileocaCommented:
oops needs to be condition.. same thing.  add is null to it

NewMi: iif Isnull([Table]![mi], "", [Table]![mi] & ".")
0
 
phileocaCommented:
sorry, in all my excitement i forgot paranthesis

NewMi: iif (Isnull([Table]![mi]), "", [Table]![mi] & ".")

there's also a Nz funtion, haven't used it in query, but i have used it in vba. try this first.

NewMi: NZ([Table]![mi], "" , [Table]![mi] & ".")
0
 
Emanon_ConsultingCommented:
You would concatenate the fields conditionally in your SQL

It would start to look like this...

SELECT tblContact.ContactID, IIf([ContactFirstName] Is Null,IIf([ContactLastName] Is Null,"No Name",[ContactLastName]),[ContactFirstName] & " " & [ContactLastName]) AS Name
FROM tblContact
ORDER BY tblContact.ContactFirstName, tblContact.ContactLastName;


Of course that's only with the First and Last Names - now you have to add the Middle Initial and the Suffix and for each build in the condition.
<grin>

I'll work on adding the rest for you if you like.

Cheers
Michael
0
 
Emanon_ConsultingCommented:
OK so here it is if you had just the Prefix (Mr. Dr. etc) and First Name and Last Name fields...

The Query builder window would like this...

Name: IIf([CustomerTitle] Is Null,IIf([CustomerFirstName] Is Null,IIf([CustomerLastName] Is Null,"No Name",[CustomerLastName]),[CustomerFirstName] & " " & [CustomerLastName]),[CustomerTitle] & " " & IIf([CustomerFirstName] Is Null,IIf([CustomerLastName] Is Null,"No Name",[CustomerLastName]),[CustomerFirstName] & " " & [CustomerLastName]))

The Actual SQL statement would look like this...
SELECT IIf([CustomerTitle] Is Null,IIf([CustomerFirstName] Is Null,IIf([CustomerLastName] Is Null,"No Name",[CustomerLastName]),[CustomerFirstName] & " " & [CustomerLastName]),[CustomerTitle] & " " & IIf([CustomerFirstName] Is Null,IIf([CustomerLastName] Is Null,"No Name",[CustomerLastName]),[CustomerFirstName] & " " & [CustomerLastName])) AS Name
FROM tblCustomer
ORDER BY tblCustomer.CustomerLastName, tblCustomer.CustomerFirstName;


Are we having fun yet???
<grin>

Cheers
Michael

I'll see if I can add the middle name field in a bit - gotta put my girls to bed now...

0
 
Emanon_ConsultingCommented:
On second thought that is an impossible (almost) SQL to write to include all possible variables for all 4 fields.
There are at least 16 variables in there (I think)

So here it is with just the Prefix and the Middle Initial...  This will assume that there is always a First and Last name at least...
Query Window
Name: IIf([CustomerTitle] Is Null,[CustomerFirstName] & " " & IIf([CustomerMiddleName] Is Null,[CustomerLastName],[CustomerMiddleName] & ". " & [CustomerLastName]),[CustomerTitle] & ". " & [CustomerFirstName] & " " & IIf([CustomerMiddleName] Is Null,[CustomerLastName],[CustomerMiddleName] & ". " & [CustomerLastName]))

And the actual SQL
SELECT IIf([CustomerTitle] Is Null,[CustomerFirstName] & " " & IIf([CustomerMiddleName] Is Null,[CustomerLastName],[CustomerMiddleName] & ". " & [CustomerLastName]),[CustomerTitle] & ". " & [CustomerFirstName] & " " & IIf([CustomerMiddleName] Is Null,[CustomerLastName],[CustomerMiddleName] & ". " & [CustomerLastName])) AS Name
FROM tblCustomer;

That's it - let us know if that is what you were looking for!

Cheers
Michael
0
 
mattturleyAuthor Commented:
Michael,

I am still getting a ". " if there is no middle initial.  Any thoughts?

For Example, one name returns -

"Mary . Grey"

Matt
0
 
mattturleyAuthor Commented:
phileoca ,

The Nz function in the query returns "Wrong number of arguments used with function in query expression ..."

The other statement in that post (NewMi: iif (Isnull([Table]![mi]), "", [Table]![mi] & ".")
) still returns a "." when there is no data in MI.

ideas?

Matt
0
 
Emanon_ConsultingCommented:
Hi there mattturley,

I tested this one here and it worked for me at home last night...
Name: IIf([CustomerTitle] Is Null,[CustomerFirstName] & " " & IIf([CustomerMiddleName] Is Null,[CustomerLastName],[CustomerMiddleName] & ". " & [CustomerLastName]),[CustomerTitle] & ". " & [CustomerFirstName] & " " & IIf([CustomerMiddleName] Is Null,[CustomerLastName],[CustomerMiddleName] & ". " & [CustomerLastName]))

Could you please paste the SQL that you are using so we can have a peek and work on the actual SQL...

Thanks
Cheers
Michael
0
 
mattturleyAuthor Commented:
I changed the one above to reflect my field names.  Here is the SQL for the entire query.

SELECT TblClassName.Dates, TblClassName.Class, TblEmployeeMaster.Last, TblEmployeeMaster.First, TblEmployeeMaster.MI, TblEmployeeMaster.Class, TblClassName.Class_Name, TblClassName.Signatory_1_Name, TblClassName.Signatory_1_Title, TblClassName.Signatory_1_Division, TblClassName.Signatory_2_Name, TblClassName.Signatory_2_Title, TblClassName.Signatory_2_Division, TblClassName.Class_Dates, TblClassName.Signatory_3_Name, TblClassName.Signatory_3_Title, TblClassName.Signatory_3_Division, IIf([Prefix] Is Null,[First] & " " & IIf([Mi] Is Null,[Last],[Mi] & ". " & [Last]),[Prefix] & ". " & [First] & " " & IIf([Mi] Is Null,[Last],[Mi] & ". " & [Last])) AS FullName
FROM TblClassName, TblEmployeeMaster
WHERE (((TblClassName.Dates)=[Forms]![frmSelection]![Dates]) AND ((TblClassName.Class)=[Forms]![frmSelection]![Class]) AND ((TblEmployeeMaster.Class)=[Forms]![frmSelection]![Class]) AND ((TblEmployeeMaster.Dates)=[Forms]![frmSelection]![Dates]));


I am still getting a ". " where there is no data in field MI.  Could it be that the field has an empty string rather than being null?  How could I account for that (ie use an or with an iif (or would it be an else))?

Thanks,
Matt
0
 
Emanon_ConsultingCommented:
OK...

So, I just tested this and it works just fine!!!
IIf([prefix] Is Null,[First] & " " & IIf([Mi] Is Null,[Last],[Mi] & ". " & [Last]),[prefix] & ". " & [First] & " " & IIf([Mi] Is Null,[Last],[Mi] & ". " & [Last])) AS FullName

These are My results from testing the SQL
ID      FullName
1      Mr.  Smith
2      John Smith
3      Mr. John Smith
4      Mr. John J. Smith

So my question would be where does the "." come from???  There is something here that we are missing I think.

I am only producing the "." in the SQL I pasted...
0
 
Emanon_ConsultingCommented:
If I could make a suggestion Matt...

Why not try creating a SQL with JUST this in it - work at it in steps to try and trouble shoot...

SELECT tblClassName.ID,IIf([prefix] Is Null,[First] & " " & IIf([Mi] Is Null,[Last],[Mi] & ". " & [Last]),[prefix] & ". " & [First] & " " & IIf([Mi] Is Null,[Last],[Mi] & ". " & [Last])) AS FullName
FROM tblClassName;

Cheers
Michael
0
 
mattturleyAuthor Commented:
As an example of what I am getting "Timothy . Elliott"

I changed only the table name in teh above SQL.  Here is the exact statement.

SELECT tblEmployeeMaster.ID,IIf([prefix] Is Null,[First] & " " & IIf([Mi] Is Null,[Last],[Mi] & ". " & [Last]),[prefix] & ". " & [First] & " " & IIf([Mi] Is Null,[Last],[Mi] & ". " & [Last])) AS FullName
FROM tblEmployeeMaster;

0
 
mattturleyAuthor Commented:
Michael,

I simplified the query by getting rid of the expressions relating to the prefix, as I don't ever use or need the prefix.  Once I did that I was able to see where I needed the Or statements.  From previous experience, I was pretty sure that the table was reading the field as a space, or as an empty string, rather than reading null.  So, the following was my final SQL solution.

SELECT tblEmployeeMaster.ID, [First] & " " & IIf([Mi] Is Null Or [MI]=" " Or [MI]="",[Last],[Mi] & ". " & [Last]) AS FullName
FROM tblEmployeeMaster;

I wouldn't have gotten the syntax without your help, so I am awarding the points to you.

Thanks for your help,
Matt
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.