I need a validation rule for a text field containing first
and last names that will force the first letter of both
first and last name to upper case and all other letters to
lower case. I am learning but have not been able to figure
this out. I also need a similar validation rule to do the
same with individual text fields for each of first and last
name. Can anyone help me with this?
Thank you
Betty
Validation rules are not capable of doing this for you. And you may
not WANT to do this: many names (McKee, MacCarthy, van Steen, de la
Cruz) do not follow this capitalization pattern.
I would STRONGLY suggest using separate FirstName and LastName fields.
This will let you index (and therefore efficiently sort or search)
each name field; you can combine the two in a query as
FullName: LastName & ", " & FirstName
or
FullName: FirstName & " " & LastName
to show "Vinson, John" or "John Vinson" as you prefer. It's much
harder to do this if you have just one field.
You can use a Form (table datasheets won't let you do this) to enter
the data and use the AfterUpdate event of the name textboxes: say you
have a textbox txtLastName; use the VBA editor to put code like this
in its AfterUpdate event:
Private Sub txtLastName_AfterUpdate()
' Don't mess with data that is already mixed case
' StrComp will check to see if the name is all lower case
If StrComp(txtLastName, LCase(txtLastName), 0) = 0 Then
Me!txtLastName = strConv(Me!txtLastName, vbProperCase)
End If
End Sub
This WILL convert "macdonald" to "Macdonald" and "van steen" to "Van
Steen" - both wrong - but you can enter them correctly in the first
place and it won't mess them up.
John W. Vinson[MVP]
