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

x
?
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
Medium Priority
?
603 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
20 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
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!

 

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 2000 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

824 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