Solved

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

Posted on 2004-08-10
16
664 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
Backup Your Microsoft Windows Server®

Backup 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.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
bind Combobox 4 29
Adding icons to a custom, shortcut menu function 6 41
Prevent users from stopping queries to import data in Access 5 29
Field behavior for "locked" form 12 29
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

911 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