ProperCase Validation

aziz rasul

Active member
Local time
Today, 00:03
Joined
Jun 26, 2000
Messages
1,935
I have a field called Surname. I want to add validation in the table design so that if the name is typed in all lowercase, Access automatically makes Proper case e.g. smith becomes Smith. Is there a validation rule I can use that will do this.

In the Validation Rule property I tried [Surname] = StrConv([Surname], 3) it doesn't do anything.

I know I can use an update query to do this but just curious as to whether it is possible in table design.
 
Here's one thought...

The validation rule is just that, a RULE; its not a function (like StrConv). I think of it as "criteria to be met", just like in a query. I would doubt that you could provide any sort of automation tool like functions as criteria. A rule ESTABLISHES correct data, where functions MANIPULATE that correct data.

Just a bit of logic for you... ;)

As an alternative, you might want to look at a mask...there are code characters to use that specify mandatory uppercase and lowercase letters.
 
I understand. I THINK from distant memory I have seen UCase being used in a validation rule so I was thinking that I could use StrConv in a similar way.

No problem, I can always use validation at the form level.
 
Good morning

The function Proper() will do the trick for you..

Best regards

Maurice


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]).
'
' See Also: StrConv Function in the
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
 
Aziz,

UCase is a function as well...but it is not a conversion of type. I wonder... 1) if it would work, and 2) if the conversion task of the function has something to do with it...???
 
Thanks MStCyr. I'll try that tomorrow and see how it goes.
 

Users who are viewing this thread

Back
Top Bottom