Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 805
  • Last Modified:

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.
0
mattturley
Asked:
mattturley
  • 7
  • 6
  • 3
1 Solution
 
mattturleyAuthor Commented:
oops, this is the second question.
0
 
phileocaCommented:
in your report query
NewMi: [Table]![mi] & "."
in your report refer to NewMi
0
 
phileocaCommented:
oops needs to be condition.. same thing.  add is null to it

NewMi: iif Isnull([Table]![mi], "", [Table]![mi] & ".")
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
Emanon_ConsultingCommented:
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:
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 7
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now