Proper Case

[Sprocket]

Proper Case Please

I'm a lousy typist so I usually format my fields to display in all one case - Usually Upper case. This is because I can't get Access to display Proper Case (Title case).
I know there is a Proper worksheet function for Excel - I use it a lot - but I can't get fields in Access to do the same.
Does anyone out there know an easy way to get Access 2000 to format fields in Proper Case.
Also is it possible to store the modified data or do I still store "Garbage case"

 

[DJN]

Here is a function to 'proper'

Function Proper(x)

' Capitalize first letter of every word in a field.
' Use in an event procedure in AfterUpdate of control;
' for example, [Last Name] = Proper([Last Name]).
' Names such as O'Brien and Wilson-Smythe are properly capitalized,
' but MacDonald is changed to Macdonald, and van Buren to Van Buren.
' Note: For this function to work correctly, you must specify
' Option Compare Database in the Declarations section of this module.

Dim Temp$, c$, OldC$, i As Integer
If IsNull(x) Then
Exit Function
Else
Temp$ = CStr(LCase(x))
' Initialize OldC$ to a single space because first
' letter needs to be capitalized but has no preceding letter.
OldC$ = " "
For i = 1 To Len(Temp$)
c$ = Mid$(Temp$, i, 1)
If c$ >= "a" And c$ <= "z" And (OldC$ < "a" Or OldC$ > "z") Then
Mid$(Temp$, i, 1) = UCase$(c$)
End If
OldC$ = c$
Next i
Proper = Temp$
End If
End Function

David

 

[Sprocket]

David - Thanks a million that works beatifully.

How do you learn this stuff?!!!

 

[Fornatian]

Does the StrConv function not suffice?

StrConv(Me.WhatEverField, vbProperCase)

 

[Sprocket]

Gosh do I feel silly - Yes StrConv() works too.

Easy when you know how!!!

Thanks all - I'll close this thread now

 

[teiben]

StrConv(Me.WhatEverField, vbProperCase)

I need to convert whatever the user has typed in a textbox to the correct case.
I put
StrConv(Me.WhatEverField, vbProperCase)
on afterupdate, but it errors out, any idea why?

 

[ghudson]

Try this in the AfterUpdate event of the text box...

TextBox1 = StrConv(TextBox1, vbProperCase)

 

[jempie]

Here is a function to 'proper'

Function Proper(x)

' Capitalize first letter of every word in a field.
' Use in an event procedure in AfterUpdate of control;
' for example, [Last Name] = Proper([Last Name]).
' Names such as O'Brien and Wilson-Smythe are properly capitalized,
' but MacDonald is changed to Macdonald, and van Buren to Van Buren.
' Note: For this function to work correctly, you must specify
' Option Compare Database in the Declarations section of this module.

Dim Temp$, c$, OldC$, i As Integer
If IsNull(x) Then
Exit Function
Else
Temp$ = CStr(LCase(x))
' Initialize OldC$ to a single space because first
' letter needs to be capitalized but has no preceding letter.
OldC$ = " "
For i = 1 To Len(Temp$)
c$ = Mid$(Temp$, i, 1)
If c$ >= "a" And c$ <= "z" And (OldC$ < "a" Or OldC$ > "z") Then
Mid$(Temp$, i, 1) = UCase$(c$)
End If
OldC$ = c$
Next i
Proper = Temp$
End If
End Function

David

David,

Just tried to use your function in an update query and got a syntax error on line:

If c$ >= "a" And c$ <= "z" And (OldC$ < "a" Or OldC$ > "z") Then

Help?!

Paul

 

[jempie]

not to worry David - using strconv instead.

Thanks.