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
355 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
  • 12
  • 8
22 Comments
 
LVL 8

Expert Comment

by:TheNautican
Comment Utility
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
Comment Utility
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
Comment Utility
Well doh! ok I'm converting this to VBA which you can then call from your query.
-Naut
0
 

Author Comment

by:jaguar5554
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 8

Accepted Solution

by:
TheNautican earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Oh dear.... I'm still running MS Access 2003 -- that must be why it is not working for me.
0
 

Author Comment

by:jaguar5554
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

763 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

9 Experts available now in Live!

Get 1:1 Help Now