Convert to Proper Case

[gschimek]

This is kind of a weird question. I downloaded and imported a table with cities and zip codes in it. All the city names are in upper case. I want to use the city names in that table as the row source for my City text box.

But in my database, I store city names in Proper Case, with just the first letter capitalized. So when I use the table as the row source, it pulls in the city name all in upper case

Is there a way, either within Access or not, to change the city names in that table to Proper Case?

 

[boblarson]

Use queries instead of tables as your recordsources and you can format them to be whatever you want. It won't matter how the data is stored.

 

[Alc]

There's a VBPropercase command, but I forget the syntax for using it. Perhaps there's something useful in Help?

 

[RuralGuy]

StrConv([YourCityField], vbProperCase)

 

[Dreamweaver]

Can't remember where I got it from might have been a book years ago but works great for me.

Function ProperCase(AnyText) As String
'Covert passed text to all lowercase. Use ProperCase()
'as you would a built-in Access function.

'If passed value is a null, ignore all the stuff below.
If IsNull(AnyText) Then
    Exit Function
End If
  
Dim IntCounter As Integer, OneChar As String

'First convert to initial cap, followed by all lowercase.
AnyText = UCase$(Left$(AnyText, 1)) & LCase$(Mid$(AnyText, 2, 255))

'Look at each character, starting at the second character.
For IntCounter = 2 To Len(AnyText)
    OneChar = Mid$(AnyText, IntCounter, 1)
    'If current character (OneChar) is a space or hyphen...
    If OneChar = " " Or OneChar = "-" Then
        '...convert the character after space/hyphen to uppercase.
        AnyText = Left$(AnyText, IntCounter) & UCase$(Mid$(AnyText, IntCounter + 1, 1)) & Mid$(AnyText, IntCounter + 2, 255)
    End If
Next

'All done, return current contents of AnyText variable.
ProperCase = AnyText
End Function