Solved

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

Posted on 2004-08-10
16
650 Views
Last Modified: 2012-08-14
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.
0
Comment
Question by:mattturley
  • 7
  • 6
  • 3
16 Comments
 

Author Comment

by:mattturley
ID: 11768015
oops, this is the second question.
0
 
LVL 11

Expert Comment

by:phileoca
ID: 11768107
in your report query
NewMi: [Table]![mi] & "."
in your report refer to NewMi
0
 
LVL 11

Expert Comment

by:phileoca
ID: 11768119
oops needs to be condition.. same thing.  add is null to it

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

Expert Comment

by:phileoca
ID: 11768167
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
 
LVL 5

Expert Comment

by:Emanon_Consulting
ID: 11769471
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
 
LVL 5

Expert Comment

by:Emanon_Consulting
ID: 11769532
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
 
LVL 5

Expert Comment

by:Emanon_Consulting
ID: 11769741
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
 

Author Comment

by:mattturley
ID: 11776811
Michael,

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

For Example, one name returns -

"Mary . Grey"

Matt
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:mattturley
ID: 11776957
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
 
LVL 5

Expert Comment

by:Emanon_Consulting
ID: 11777248
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
 

Author Comment

by:mattturley
ID: 11777336
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
 
LVL 5

Expert Comment

by:Emanon_Consulting
ID: 11777711
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
 
LVL 5

Expert Comment

by:Emanon_Consulting
ID: 11777731
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
 

Author Comment

by:mattturley
ID: 11777848
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
 
LVL 5

Accepted Solution

by:
Emanon_Consulting earned 200 total points
ID: 11779426
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
 

Author Comment

by:mattturley
ID: 11788620
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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
microsoft access - xml 10 26
Error compiling fairly simple database 3 16
Dlookup issue 4 5
Query 14 0
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now