Solved

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

Posted on 2004-08-10
16
707 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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
 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

756 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