Solved

Conversion for all caps to proper case function (StrConv) for names that may contain more than one capital and/or punctuation (e.g. McDonald's/HD23)

Posted on 2014-02-18
22
520 Views
Last Modified: 2016-05-17
Is anyone aware of stock code that will convert text from all caps to proper case but also account for names that may contain more than one capital and/or punctuation. For example, convert MCDONALD'S/HD23 to McDonald's/HD23. Examples of punctuation include "-", "'", "/", etc.

I found the following in a previous post but it does not account for multiple capitals, and only capitalizes after the first apostrophe (e.g. correct: O'Grady's; incorrect: McDonald'S):

IIf(Instr([fieldname],"-"),StrConv(Left([fieldname],Instr([fieldname],"-")-1),3) & "-" & StrConv(Mid([fieldname],Instr([fieldname],"-")+1),3), IIf(Instr([fieldname],"'"), StrConv(Left([fieldname],Instr([fieldname],"'")-1),3) & "'" & StrConv(Mid([fieldname],Instr([fieldname],"'")+1),3), StrConv([fieldname],3)))

Any help is appreciated. Thanks.
0
Comment
Question by:jaguar5554
[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
  • 12
  • 8
22 Comments
 
LVL 8

Expert Comment

by:TheNautican
ID: 39869156
Proper() gets you close but some names still wont convert correctly. This looks like it addresses that issue. As for the ending 's getting capitalized you should be able to either code that into your look checking the right 2 most characters and changing as necessary or just do a find and replace after the fact.

http://dmcritchie.mvps.org/excel/proper.htm

Regards,
-Naut
0
 

Author Comment

by:jaguar5554
ID: 39869214
Thank you for the link -- great information but I was looking for something that could be run in a MS Access query. Any other suggestion(s) is greatly appreciated!
0
 
LVL 8

Expert Comment

by:TheNautican
ID: 39870272
Well doh! ok I'm converting this to VBA which you can then call from your query.
-Naut
0
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 

Author Comment

by:jaguar5554
ID: 39870331
Thank you so much! You're probably wondering why I did not convert it myself -- I am not a programmer and would not know where to begin! Again, much thanks!
0
 
LVL 8

Expert Comment

by:TheNautican
ID: 39870418
Ok, this is just about done. One last question, will the names always end with the / and then 4 digits or does the differ name to name?

-Naut
0
 

Author Comment

by:jaguar5554
ID: 39870921
Hello Naut, the names differ -- some have slashes and some dashes and some pound signs, and some names are not followed by anything. Thank you for your assistance -- I appreciate that it is quite a task what you are doing!
0
 
LVL 8

Expert Comment

by:TheNautican
ID: 39871074
Ok, to be clear, the names will be in one of the following formats:

Name ending with 's
Name ending with 's then one of the delimiters you mentioned plus some suffix

Is this correct? If there are other formats let me know. I'm just trying to come up with a way to split the name from the suffix, if there is a suffix.

-Naut
0
 

Author Comment

by:jaguar5554
ID: 39872029
Hello again (sorry for the delay in responding but I've been in meetings...). To answer yours, these are actually business names so there is not a great chance there will be a suffix. I scanned through the data and found mostly @, *, #, %, (, /, etc. (and if there isn't a space after the punctuation, the next following letter is lower case but should be upper case. There are also many names that should have mutliple capitals (e.g. O'Grady, O'Neill, McDonald, etc.). And lastly I found many names with an apostrophe "s" and the "s" is incorrectly capitalized. I hope this helps and is not too confusing to follow. Again, thank you much!
0
 
LVL 8

Expert Comment

by:TheNautican
ID: 39873552
Ok. Here it is. I wish there were a more concrete way to test the end, but hopefully this gets you want you want. Let me know of any names that don't display correctly and we'll see about tweaking the function.

Just paste the contents of this file into a new module for your database. Then in your query on a blank field type something like Proper Name:MakeProper([tblname]![fieldname]) where tblname = the name of the table your name field is in and fieldname=the name of the field that contains the data you want to run through the function.

-Naut
make-proper.txt
0
 

Author Comment

by:jaguar5554
ID: 39873921
Hello again, Wow! that's awesome -- thank you! As I scroll through the query results, I get a Run-time error '9' subscript out of range, and it stops at the following line in the code:         strRight = arrySplitString(1). I also noticed at the the first letter after a hyphen does not capitalize (e.g. 7-eleven). I searched the error and it seems to refer back to Excel. Thoughts? (because I'm certainly stumped!) thank you!!
0
 
LVL 8

Accepted Solution

by:
TheNautican earned 500 total points
ID: 39874260
Sorry about that. The only thing causing that would be names that don't contain a space in them,  7-Eleven works fine in my database but just like a customer named Brian#123 the first part of the function fails since it doesn't contain a space. We can fix this be checking for a duplicate and taking only one. See attachment.
make-proper.txt
0
 

Author Comment

by:jaguar5554
ID: 39874367
Hi Naut, I'm not sure what I'm doing wrong but I do not believe the code is working. For names like 7-ELEVENT, MCDONALD'S, (FOX HUT), the query returns 7-eleven, Mcdonald's, (fox Hut), etc. Did I forget to do something?
0
 
LVL 8

Expert Comment

by:TheNautican
ID: 39874737
Hmm, Im using Access 2010 and the 3 names you give i get:
7-ELEVEN -> 7-Eleven
MCDONALD'S -> McDonald's
(FOX HUT) - > (fox Hut)

My query has this in it to get those names
Proper Name: MakeProper([tblMain]![Name])

Open in new window


The capitalization aside, do you want to remove the "()" and those other punctuations you told me about? For now I'm using the to separate valid last name from extra stuff, like in your first example MCDONALD'S/HD23.
0
 

Author Comment

by:jaguar5554
ID: 39877412
Oh dear.... I'm still running MS Access 2003 -- that must be why it is not working for me.
0
 

Author Comment

by:jaguar5554
ID: 39878602
Hello again~ I've combed the code you provided trying to identify what it is in it that runs only in MS Access 2010. Needless to say -- without any luck. I hope you haven't given up on this request -- I appreciate your help on this. Thank you :-)
0
 

Author Comment

by:jaguar5554
ID: 39896217
Hello again! I obtained a copy of Access 2010, imported my table and the code you provided into the new database, but still no proper case as you describe above. Are there references that should be added in order for the module to proper execute? Compiling and compacting/repairing the database did not help. Let me know... I so would like the proper case function to work on my dataset. Much thanks and appreciation for your assistance.  Thank you!
0
 
LVL 8

Expert Comment

by:TheNautican
ID: 39896549
Sorry I've been out sick but ill check the database when i'm back in the office to see if there are any references or anything else

-Naut
0
 

Author Comment

by:jaguar5554
ID: 39897711
Thank for again for your assistance. In the meantime, here are the references in my version of Access 2010:
Visual Basic for Applications
Microsoft Access 14.0 Object Library
OLE Automation
Microsoft Office 14.0 Access database engine object

Get well soon!
Andrea
0
 

Author Comment

by:jaguar5554
ID: 39978931
Hello again, I am still trying to get the code to work. In MS Access 2010, I get the following when running the query: Undefined function 'MakeProper' in Expression. I checked References and there are none listed as "MISSING." Any thoughts?
0
 

Author Comment

by:jaguar5554
ID: 39978957
Update: ignore the "undefined function" comment above. I adjusted my security/macro settings (enabling content) which resolved the undefined function error. However, the function does make proper those names as described above or in the code. Any additonal assistance is greatly appreciated! Thanks in advance :-)
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

622 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