Using StrConv function to transform text to Proper

[bearcub]

I have a database where I have several fields text in different formats (i.e. names that are all capitalized or words that are all lower case or words that are in proper case).

I would like to convert all the records to Proper Case so they all follow the same format.

In Excel, I've used the Proper function to do this but it seems that this function doesn't exist in Access. So, I found that "StrConv" performs the same task as the Proper function.

This function works great but I did find out that names that use hyphens - like in married names have a minor issue. StrConv function converts all letters appearing after a hyphen in lower case - it seems to consider a hyphen as a letter.

Example: "Rose-Williams" or "ROSE-WILLIAMS" or "rose-williams" becomes Rose-williams after the StrConv function is applied to the field - which is not what I want. Rose-Williams is the output that I need.

Is there a work around to this so that all names are in proper case and the words after a hyphen are capitalized or am I not using this function correctly. I am using 3 as the 2nd parameter.

Thank you for your help,

Michael

 

[Joe4]

See the code here: https://answers.microsoft.com/en-us...e/8370ebc4-729f-47cc-a24d-92c4306c8911?auth=1

 

[bearcub]

thank you Joe4. I used the function in my query and it worked great.

Can I use this function in my original table -- it's linked to an Excel table which contains data that is dumped from the company database we use at work.

Thank you,

Michael

 

[Joe4]

I don't know, you would have to try.
The newer versions of Excel allow you to use Calculated fields in Tables, though it is not recommended and I would never do it. It kind of violates of rules of data normalization.
Typically, all calculations should be done on the Query level as opposed to the Table level.
But, if you have a newer version of Excel, and would like to try, see what happens. I am not sure if User Defined Functions will work in Access tables though.