This may be a stupid question, but I inherited a database with tables imported from an old FoxPro program. All of the data is in upper case. Is there a way to convert the data within the tables to Initial Capitol case?

Thanks a million!!

1st, make a copy of your table!!!!!!!!!!!!!!!!

Paste this into a module, then run an update query and update your field to
SetInitialCaps([fieldname])

Function SetInitialCaps(TextIn As Variant)

Dim WorkingText As String
Dim LoopCount As Integer
Dim SpaceFlag As Integer
Dim i As Integer


If IsNull(TextIn) Then GoTo SetInitialCaps_Exit

TextIn = Trim(TextIn)

If Len(TextIn) > 0 Then
WorkingText = UCase(Left(TextIn, 1))
If InStr(TextIn, " ") = 0 Then
SetInitialCaps = WorkingText & LCase(Mid(TextIn, 2))
GoTo SetInitialCaps_Exit
End If

LoopCount = Len(TextIn)
SpaceFlag = 0

For i% = 2 To LoopCount
If SpaceFlag = 0 Then
WorkingText = WorkingText & LCase(Mid(TextIn, i%, 1))
Else
WorkingText = WorkingText & UCase(Mid(TextIn, i%, 1))
End If

If Mid(TextIn, i%, 1) = " " Then
SpaceFlag = 1
Else
SpaceFlag = 0
End If
Next i%
SetInitialCaps = WorkingText
Else
SetInitialCaps = Null
End If

SetInitialCaps_Exit:
Exit Function

End Function

HTH!!!
Lorraine

I do not think that there is a built in function to return proper case

But here is a function that will do what you want
Paste it into a standard module and use it wherever you wish



Hope It Helps

Be aware that it won't work on some things like
SMITH-JONES will come out Smith-jones
MCCORMICK will come out Mccormick

You can also use :
StrConv([YourField],3)

StrConv, as Francois pointed out, will enable you to convert a string to different 'cases'. Help in Access will provide more details.

While StrConv(YourStringHere,3) will work, I prefer StrConv(YourStringHere,vbProperCase) as it makes it easier to read.

I honestly don't use the StrConv anymore though. I know there's at least one demo/function in the Code Archive forum that can help with proper capitalisation (spelled with an S just for RCubed! (IMG:http://www.utteraccess.com/forum/style_emoticons/default/smile.gif) ).

HTH
Noah

I prefere also to use StrConv(YourStringHere,vbProperCase) if I use it in code.
But if you use it in an update query, you have to use StrConv([YourField],3).

Good point Francois!

(IMG:http://www.utteraccess.com/forum/style_emoticons/default/smile.gif)

Noah

Thanks to everyone for the code needed to change these text cases! While I have a lot of experience with Access, I am a newbie to code. I appreciate all the help!