Wendy writes to ask:
“In Excel, I can use the PROPER function to change my text to proper case. Why is there is no PROPER function in Access? “
Wendy has stumbled on one of the mysteries of life – there are many functions you use in Excel that are not found in Access. Well…it’s not really a mystery. I actually have an idea why this may be.
The Access “Jet” engine was originally developed by the SQL Server team, outside the realm of Excel. So a majority of the Access functions you can use are closely related to those you can use in SQL Server. This inevitably meant that Excel and Access would use different functions to accomplish certain tasks. Converting text to upper, lower, and proper case falls into this category. 
So if Wendy wanted to convert her text to proper case in Access, she would need to use the STRCONV.
Using the StrConv function
For those of you who have never used the StrConv function, it does need a bit of explaining. The StrConv function allows you to convert a string to a specified conversion setting such as uppercase, lowercase, or proper case. For example, StrConv(“my text”,3) would be converted to proper case, reading “My Text”.
Here is the basic syntax:
StrConv(Target String, Conversion Type)
Target String is the string to be converted. In a query environment, you can use the name of a field to specify that you are converting all the row values of that field.
Conversion type specifies how to convert the string. The following constants identify the conversion type.
1 – Converts the string to uppercase characters.
2 – Converts the string to lowercase characters.
3 – Converts the first letter of every word in string to uppercase.
You would typically use the StrConv function in an update query. This query would convert all the values in the Address field to proper case.
Awesome tip. I had no idea this function existed in Access.
I am assuming that there is no fourth option that will just allow you to capitalize the first letter of a string, as you only mentioned the three options, but it would be nice.
‘Capitalize first letter of mystring
Ucase(Left(mystring,1)) & Right(mystring,Len(mystring)-1)
Hmmm… I thought it would be trivial
Can anyone improve?
Mind you STRCONV and PROPER aren’t the same, they don’t give the same results.
Andrew: How do you mean? Do you have an example?
@datapig,
One difference off the top of my head is single words with embedded periods. For example…
one.two.three
@datapig,
I should clarify my last posting. I don’t know if what I posted holds in Access… I don’t work with Access… what I posted holds for Excel (which is what I thought we were talking about until I went back and reread the blog article)… the worksheet function PROPER and the VBA function StrConv treat single words with embedded periods (dots) in them differently.
Thank you!!
While over a year late, here is an answer to Ken’s challenge for an improvement to his “sentence casing” code line….
Ucase(Left(mystring,1)) & Mid(mystring, 2)