Help with Access 97 VBA 2

[JodieK]

Hello

I'm new to vba and would appreciate some help with the following prob: I have a text field in a form, and I want to ensure that the each word in the field begins with a capital letter and the rest is lower case.

This is the code I have so far, I'd really appreciate suggestions J


Private Sub txtAddress1_AfterUpdate()

'address shows first letter of each word with a capital the rest in lower case

Dim spaceposition As Integer
Dim addresspart As Integer
Dim Secondletter As Integer

spaceposition = InStr(1, Me.txtAddress1, " &quot
addresspart = Right(Me.txtAddress1, Len(Me.txtAddress1) - spaceposition)
Secondletter = Len(addresspart) - 1
Me.txtAddress1.Value = LCase(Me.txtAddress1.Value)
Me.txtAddress1.Value = UCase(Left(Secondletter, 1))

End Sub

 

[baronvont]

Hi JodieK,

There are no doubt many ways to do this, but here's a piece of code (not mine!) that will capitalise any sentence..

to call it use the command Proper(anytext) in a sub

Function Proper(anyValue As Variant) As Variant
' Accepts: a text value
' Purpose: converts first letter of each word to uppercase
' Returns: converted text value
' Note: although this function converts most proper names correctly, it converts
' 'McKee' to 'Mckee', 'van Buren' to 'Van Buren', 'John III' to 'John Iii'

Dim ptr As Integer
Dim theString As String
Dim currChar As String, prevChar As String

If IsNull(anyValue) Then
Exit Function
End If

theString = CStr(anyValue)
For ptr = 1 To Len(theString) 'Go through string char by char.
currChar = Mid$(theString, ptr, 1) 'Get the current character.

Select Case prevChar 'If previous char is letter,
'this char should be lowercase.
Case "A" To "Z", "a" To "z"
Mid(theString, ptr, 1) = LCase(currChar)

Case Else
Mid(theString, ptr, 1) = UCase(currChar)

End Select
prevChar = currChar
Next ptr
Proper = CVar(theString)

End Function