473,320 Members | 1,978 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Capitalising names

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
Nov 12 '05 #1
5 4598
DFS
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

Nov 12 '05 #2
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


Nov 12 '05 #3
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.
Nov 12 '05 #4
Thanks Fred, I'lll check that out when the hectic work settles down a bit.
Sounds reasonable.

dixe

"fredg" <fg******@example.invalid> wrote in message
news:%L***********************@bgtnsc04-news.ops.worldnet.att.net...
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.

Nov 12 '05 #5
You're right you are going to have to check a few rules in order to achieve
this.

StrConv will get you part of the way there but it will not handle
O'Neil
McTavish
MacMillan

etc.

Something like this get's close
' ****************************************
' Code Start
Function ProperName(ByVal NameIn)
NameIn = Replace(NameIn, " ", " ", Compare:=vbBinaryCompare)
NameIn = StrConv(NameIn, vbProperCase)

NameIn = FixName(NameIn, "'")
NameIn = FixName(NameIn, " mac")
NameIn = FixName(NameIn, " mc")
' Add other markers as appropriate

ProperName = NameIn
End Function

Private Function FixName(ByVal NameIn, Marker)
Dim intInstr As Integer

intInstr = 1
intInstr = InStr(intInstr, NameIn, Marker, vbTextCompare)
Do While intInstr <> 0
intInstr = intInstr + Len(Marker)
If Len(intInstr) <= Len(NameIn) Then
Mid(NameIn, intInstr, 1) = UCase(Mid(NameIn, intInstr, 1))
End If
intInstr = InStr(intInstr, NameIn, Marker, vbTextCompare)
Loop
FixName = NameIn
End Function
' Code End
' ****************************************

Sample call
' ****************************************
' Code Start
Function TestProperName()
Debug.Print ProperName("JOHN MCTAVISH")
Debug.Print ProperName("JOHN O'NEIL")
Debug.Print ProperName("JOHN MACTAVISH")
End Function
' Code End
' ****************************************
Terry
"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

Nov 12 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Kari Laitinen | last post by:
During the past 15 years I have been writing computer programs with so-called natural names, which means that the names (identifiers, symbols) in progarms are constructed of several natural words....
1
by: Jens Riedel | last post by:
Hello, we are planning to port an application from one database to different others, including DB2. While reading the GettingStarted document for DB2 Personal Edition I got a little confused...
0
by: Mythran | last post by:
I wrote some code that is supposed to enumerate through the specified file's win32 resources and return a string-array of all icon names. When it runs, it returns a string-array with a bunch of...
7
by: Petr Jakes | last post by:
I have got names of functions stored in the file. For the simplicity expect one row only with two function names: printFoo, printFOO In my code I would like to define functions and then to read...
20
by: Shawnk | last post by:
I would like to get the class INSTANCE name (not type name) of an 'object'. I can get the object (l_obj_ref.GetType()) and then get the (l_obj_typ.Name) for the class name. I there any way of...
3
by: cybernerdsx2 | last post by:
Hi, I notice a function prototype being declared as following: FileStream.h ========= extern void openFile(char *__ident, int __option); But, in the function declaration part shown as...
38
by: John Salerno | last post by:
Here's my script: import sqlite3 con = sqlite3.connect('labdb') cur = con.cursor() cur.executescript(''' DROP TABLE IF EXISTS Researchers; CREATE TABLE Researchers ( researcherID...
16
by: per9000 | last post by:
Hi, I recently started working a lot more in python than I have done in the past. And I discovered something that totally removed the pretty pink clouds of beautifulness that had surrounded my...
4
by: Steven Simpson | last post by:
Stefan Ram wrote (in "More than one language in a page"): Is this a new trend of user-agent writers (Microformats, and now Google) staking claims on the @class namespace? I'm surely not the only...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.