dixie wrote:
I can't see enough options there. All I can find is the option to
capitalise the first letter of a new word which I can already do. One other
option I omitted from the first message was to capitalise the first letter
following mc for scottish names.
So what I still need is the ability to capitalise the first letter after a
hyphen, after an apostrophe and after the letter combination 'mc' (probably
were the m in mc is the first letter of a word). I fear that there is no
simple way to do this.
dixie
"DFS" <no******@nospam.com> wrote in message
news:vt************@corp.supernews.com... Dixie,
Take a look at the StrConv() function, and the vbProperCase setting.
"dixie" <di****@dogmail.com> wrote in message
news:ly*****************@nnrp1.ozemail.com.au... I have recently received data for a database where there are over 1000
people's names, all in capital letters (surnames and first names). I
really need to be able to convert them all to lower case. I figure that I need a query that will convert the first letter of a name to a capital letter.
Another to convert any letter following a space to a capital. Another to convert any letter following an apostrophe (') to a capital and yet
another to convert any letter following a hyphen (-) to a capital.
Any ideas?
dixie
Dixie,
Here is a method of converting all names to Proper Case, and allow for
exceptions, such as McDonald and O'Connor, etc.
Make a new table.
Field: [ID] AutoNumber Indexed No Duplicates
Field: [ExceptName] Text
TableName: tblExceptions
Enter as many known name exceptions as you can (names that require 2
capitals as well as names which must all be in lower case).
====
Paste this function into a new module.
Public Function ConvExceptions(strIn As String) As String
' Will find exceptions to Proper Case capitalization of names.
On Error Resume Next
If DCount("*", "tblExceptions", "[ExceptName] = " & Chr(34) & strIn &
Chr(34) & "") > 0 Then
Dim intResponse As Integer
Dim strFind As String
strFind = DLookup("[ExceptName]", "tblExceptions", "[ExceptName] = "
&
Chr(34) & strIn & Chr(34) & "")
intResponse = MsgBox(strFind & vbCrLf & " is an exception name." &
vbCrLf _
& " Accept the above capitalization? Y/N ?", vbYesNo, "Exception
found!")
If intResponse = vbYes Then
ConvExceptions = strFind
Exit Function
End If
End If
ConvExceptions = StrConv(strIn, 3)
End Function
======
Call it from a Query:
Exp:ConvExceptions([FieldName])
Set the criteria for [FieldName] to
Is Not Null
Be prepared to respond to the message box if an exception is found.
or use it in an Control's AfterUpdate event:
If Not IsNull([ThisField]) Then
[ThisField] = ConvExceptions([ThisField])
End If
Add new names to the exceptions table as they occur.
Also remember that there are multiple correct capitalizations
of names, O'Connor and O'connor are both correct, depending
upon the individual, and some words can be capitalized or not,
depending upon usage i.e. "The city's main street is Main Street.".
--
Fred
Please reply only to this newsgroup.
I do not respond to personal e-mail.