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)

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.
jaguar5554Business AnalystAsked:
Who is Participating?
 
TheNauticanConnect With a Mentor Commented:
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
 
TheNauticanCommented:
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
 
jaguar5554Business AnalystAuthor Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
TheNauticanCommented:
Well doh! ok I'm converting this to VBA which you can then call from your query.
-Naut
0
 
jaguar5554Business AnalystAuthor Commented:
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
 
TheNauticanCommented:
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
 
jaguar5554Business AnalystAuthor Commented:
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
 
TheNauticanCommented:
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
 
jaguar5554Business AnalystAuthor Commented:
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
 
TheNauticanCommented:
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
 
jaguar5554Business AnalystAuthor Commented:
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
 
jaguar5554Business AnalystAuthor Commented:
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
 
TheNauticanCommented:
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
 
jaguar5554Business AnalystAuthor Commented:
Oh dear.... I'm still running MS Access 2003 -- that must be why it is not working for me.
0
 
jaguar5554Business AnalystAuthor Commented:
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
 
jaguar5554Business AnalystAuthor Commented:
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
 
TheNauticanCommented:
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
 
jaguar5554Business AnalystAuthor Commented:
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
 
jaguar5554Business AnalystAuthor Commented:
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
 
jaguar5554Business AnalystAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.