I copied someone's code from this forum for change a field that was in all capitals to first letter upper, rest of field lower. Now I cannot find that person's name or email. My new problem to correct: code for changing an address field to what would be Title Case in Word....first letter of each word in the address field should be capitalized. Although that does not solve the problem of addresses such as NE 1st Place, that would then come out Ne 1st Place. Hmmm, anyone have any suggestions? Maybe I should change this field to all caps? How do I do that? Surely there are other who have had this problem in their databases? Thanks for any help and your time!
To change a string in all uppercase use the ucase() function.
Here's a function to change every first letter of a word to upper case
Function fFirstUpper(ByVal vstrIn As String) As String
Const cSpace As String = " "
Dim strOut As String
Dim strCur As String
Dim lngPos As Long
Dim blnNewWord As Boolean
blnNewWord = True
strOut = ""
For lngPos = 1 To Len(vstrIn)
strCur = Mid(vstrIn, lngPos, 1)
If strCur = cSpace Then
strOut = strOut & strCur
blnNewWord = True
If blnNewWord Then
strOut = strOut & UCase(strCur)
blnNewWord = False
strOut = strOut & LCase(strCur)
'strOut = strOut & strCur
'if you want to keep case of characters within a word
fFirstUpper = strOut
Are your fields all lowercase or mixed case currently?
Apel, thank you for taking time to write out this code to solve my problem. Right now the contents of this field are lower case except for first letter in field but in most cases that first character is a number because this is an address field. I thought I'd be able to figure out how to make this code run and change everything in the field but I have 0% experience in doing Visual Basic work. So as yet I have not been able to make it work. The other procedure in which I was able to change my field contents used an update to expression in an update query. What I have is 26,000 records in which the address fields need to be updated with proper case for each one. Confusing the issue is that nearly all of these address fields have numbers and letters. Some have directional abbreviations such as NE for North East so if I use a code (if I can figure out how to make it work) it would make it look like this: Ne which wouldn't be correct either. Should I just forget this whole thing and figure out an update query to change everything to all upper case? I just don't know if it's possible to do what I want or not. example:
4545 nw 104th place apt 244 should be changed to
4545 NW 104th Place Apt 244 or either
4545 NW 104TH PLACE APT 244
Thanks again for your time.....you and the other people helping out us inexperienced Access users are ANGELS.
Changing all to uppercase is possible, detection for abbreviations and stuff will probably be quite a lot of code (maybe work with a table of known abbreviations). But here's the SQL for the uppercase query:
Thanks again! I have changed the field to all uppercase now. No problem with that function. I do agree -- I think it's so many exceptions and abbreviations that for now I'm just going to leave them all uppercase.