'UPPER CASE' to 'Normal Writing'

[Andy Chalkley]

Hi
I need to convert surname and firstname fields in a table from upper case to
normal.
There are about 10000 records.
Typically:

DE ANTONIO De Antonio
CONROY, R W AND B L Conroy, R W and B L
BURKE Burke
SMITH Smith
M & B SERVICES M & B Services

I can write a query to put them all in lower case.
NAME1: Format([NAME],"<")
I can't puzzle a way of getting the first letter as upper case.

Thanks in advance.
Andy Chalkley
Perth West Australia

[Douglas J. Steele]

Re: 'UPPER CASE' to 'Normal Writing'

NAME1: UCase(Left([NAME], 1)) & LCase(Mid([NAME], 2))

Unfortunately, it's not that simple, though. How are you going to handle
McDonald or von Beethoven?

--
Doug Steele, Microsoft Access MVP

http://I.Am/DougSteele

(No private e-mails, please)



"Andy Chalkley" <andy.chalkley@ yescomputer.com .au> wrote in message
news:40081d7e$1 @quokka.wn.com. au...[color=blue]
> Hi
> I need to convert surname and firstname fields in a table from upper case[/color]
to[color=blue]
> normal.
> There are about 10000 records.
> Typically:
>
> DE ANTONIO De Antonio
> CONROY, R W AND B L Conroy, R W and B L
> BURKE Burke
> SMITH Smith
> M & B SERVICES M & B Services
>
> I can write a query to put them all in lower case.
> NAME1: Format([NAME],"<")
> I can't puzzle a way of getting the first letter as upper case.
>
> Thanks in advance.
> Andy Chalkley
> Perth West Australia
>
>
>
>[/color]

[Trevor Best]

Re: 'UPPER CASE' to 'Normal Writing'

On Sat, 17 Jan 2004 00:08:05 +0800 in comp.databases. ms-access, "Andy
Chalkley" <andy.chalkley@ yescomputer.com .au> wrote:
[color=blue]
>Hi
>I need to convert surname and firstname fields in a table from upper case to
>normal.
>There are about 10000 records.
>Typically:
>
>DE ANTONIO De Antonio
>CONROY, R W AND B L Conroy, R W and B L
>BURKE Burke
>SMITH Smith
>M & B SERVICES M & B Services
>
>I can write a query to put them all in lower case.
>NAME1: Format([NAME],"<")
>I can't puzzle a way of getting the first letter as upper case.
>
>Thanks in advance.
>Andy Chalkley
>Perth West Australia[/color]

StrConv([NAME],vbProperCase)

(in a query, use StrConv([NAME],3))

But as Douglas & Chuck pointed out, you'll have a problem with names
like McDonald, etc.

--
A)bort, R)etry, I)nfluence with large hammer.

[Tom Travolta]

Re: 'UPPER CASE' to 'Normal Writing'

"Chuck Grimsby" <c.grimsby@worl dnet.att.net.in valid> wrote in message
news:pbrg00l3lj us7ahv2h4gr5r2l jr3hin7fd@4ax.c om...[color=blue]
>
> Not to mention Jr, Sr, III, etc.
>
> Or how about:
>
> "Right Reverend John Paul Jones III, DR., DDS."[/color]


You know him too?
He still owes me $50 from high school.

[Jaeymeson]

Re: 'UPPER CASE' to 'Normal Writing'

Go to www.microsoft.com knowledgebase and locate the article Microsoft
Knowledge Base Article - 110391


you can create or copy and paste the function in that article to help
with your problems.


"Andy Chalkley" <andy.chalkley@ yescomputer.com .au> wrote in message news:<40081d7e$ 1@quokka.wn.com .au>...[color=blue]
> Hi
> I need to convert surname and firstname fields in a table from upper case to
> normal.
> There are about 10000 records.
> Typically:
>
> DE ANTONIO De Antonio
> CONROY, R W AND B L Conroy, R W and B L
> BURKE Burke
> SMITH Smith
> M & B SERVICES M & B Services
>
> I can write a query to put them all in lower case.
> NAME1: Format([NAME],"<")
> I can't puzzle a way of getting the first letter as upper case.
>
> Thanks in advance.
> Andy Chalkley
> Perth West Australia[/color]

[Trevor Best]

Re: 'UPPER CASE' to 'Normal Writing'

On Sat, 17 Jan 2004 01:00:30 +0000 (UTC) in comp.databases. ms-access,
"Tom Travolta" <tom@travolta.c om> wrote:
[color=blue]
>"Chuck Grimsby" <c.grimsby@worl dnet.att.net.in valid> wrote in message
>news:pbrg00l3l jus7ahv2h4gr5r2 ljr3hin7fd@4ax. com...[color=green]
>>
>> Not to mention Jr, Sr, III, etc.
>>
>> Or how about:
>>
>> "Right Reverend John Paul Jones III, DR., DDS."[/color]
>
>
>You know him too?
>He still owes me $50 from high school.[/color]

I was wondering about that bloke called "Jr Sr III", that's worse than
Major Major major from Catch 22.

--
A)bort, R)etry, I)nfluence with large hammer.

[Andy Chalkley]

Re: 'UPPER CASE' to 'Normal Writing'

Magic.
I also found a similar script that works a treat.
It also picks up - and '
Problem solved.
Thanks
Andy Chalkley

############### ###########
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

############### ###########

I used the above with:

############### ###########
Private Sub Command0_Click( )
Dim rs As Recordset
Dim sQuery As String
Dim dbCurrent As Database
Dim iCounter As Integer

sQuery = "SELECT * FROM tblRegister"
Set dbCurrent = CurrentDb
Set rs = CurrentDb.OpenR ecordset(sQuery )
DoCmd.Hourglass True
With rs
rs.MoveFirst
Do Until .EOF 'Loop through Register table.
rs.Edit
rs.Fields("NAME ") = Proper(.Fields( "NAME"))
rs.Update
rs.MoveNext 'Move to the next record
iCounter = iCounter + 1
Loop
End With 'rsReg
DoCmd.Hourglass False
Set rs = Nothing
End Sub
############### ###########






"Jaeymeson" <Jaeymeson@hotm ail.com> wrote in message
news:191bb0e6.0 401161715.659db d22@posting.goo gle.com...[color=blue]
> Go to www.microsoft.com knowledgebase and locate the article Microsoft
> Knowledge Base Article - 110391
>
>
> you can create or copy and paste the function in that article to help
> with your problems.
>
>
> "Andy Chalkley" <andy.chalkley@ yescomputer.com .au> wrote in message[/color]
news:<40081d7e$ 1@quokka.wn.com .au>...[color=blue][color=green]
> > Hi
> > I need to convert surname and firstname fields in a table from upper[/color][/color]
case to[color=blue][color=green]
> > normal.
> > There are about 10000 records.
> > Typically:
> >
> > DE ANTONIO De Antonio
> > CONROY, R W AND B L Conroy, R W and B L
> > BURKE Burke
> > SMITH Smith
> > M & B SERVICES M & B Services
> >
> > I can write a query to put them all in lower case.
> > NAME1: Format([NAME],"<")
> > I can't puzzle a way of getting the first letter as upper case.
> >
> > Thanks in advance.
> > Andy Chalkley
> > Perth West Australia[/color][/color]

[Andy Chalkley]

Re: 'UPPER CASE' to 'Normal Writing'

Perhaps I should modify it to cope with McDribble and van der Wobble
Andy Chalkley

[rkc]

Re: 'UPPER CASE' to 'Normal Writing'


"Andy Chalkley" <andy.chalkley@ yescomputer.com .au> wrote in message
news:400a3dac$1 @quokka.wn.com. au...[color=blue]
> Perhaps I should modify it to cope with McDribble and van der Wobble
> Andy Chalkley[/color]

Post back on how you implement that please.

[Andy Chalkley]

Re: 'UPPER CASE' to 'Normal Writing'

This Capitalise function works well and leaves very few records to adjust.
Andy Chalkley


#########
Function Proper(X)
' Capitalize first letter of every word in a field.
' Convert McDonald and other name features
' Option Compare Database in the Declarations section of this module.
Dim sString, sCurrent, sPrevious, sPreviousPrevio us,
sPreviousPrevio usPrevious, iCounter As Integer
Dim iLength As Integer

If IsNull(X) Then
Exit Function
Else
sString = CStr(LCase(X))
sPrevious = " "
sPreviousPrevio us = ""
sPreviousPrevio usPrevious = ""
iLength = Len(sString)
For iCounter = 1 To iLength
sCurrent = Mid$(sString, iCounter, 1)
If sCurrent >= "a" And sCurrent <= "z" And (sPrevious < "a" Or
sPrevious > "z") Then
Mid$(sString, iCounter, 1) = UCase$(sCurrent )
End If
'FIX MCDRIBBLE
If sCurrent >= "a" And sCurrent <= "z" And sPrevious = "c" And
sPreviousPrevio us = "M" And sPreviousPrevio usPrevious = " " Then
Mid$(sString, iCounter, 1) = UCase$(sCurrent )
End If
'FIX PO BOX
If sCurrent = " " And sPrevious = "o" And sPreviousPrevio us = "P"
And iCounter = 3 Then
Mid$(sString, 2, 1) = "O"
End If
'FIX NT
If sCurrent = "t" And sPrevious = "N" And sPreviousPrevio us = " "
And iCounter = iLength Then
Mid$(sString, iCounter, 1) = "T"
End If
'FIX NSW
If sCurrent = "w" And sPrevious = "s" And sPreviousPrevio us = "N"
And sPreviousPrevio usPrevious = " " And iCounter = iLength Then
Mid$(sString, iCounter, 1) = "W"
Mid$(sString, iCounter - 1, 1) = "S"
End If
'FIX QLD
If sCurrent = "d" And sPrevious = "l" And sPreviousPrevio us = "Q"
And sPreviousPrevio usPrevious = " " And iCounter = iLength Then
Mid$(sString, iCounter, 1) = "D"
Mid$(sString, iCounter - 1, 1) = "L"
End If
'FIX SA
If sCurrent = "a" And sPrevious = "S" And sPreviousPrevio us = " "
And iCounter = iLength Then
Mid$(sString, iCounter, 1) = "A"
End If
'FIX WA
If sCurrent = "a" And sPrevious = "W" And sPreviousPrevio us = " "
And iCounter = iLength Then
Mid$(sString, iCounter, 1) = "A"
End If
'FIX Smith'S
If sCurrent = " " And sPrevious = "S" And sPreviousPrevio us = "'"
And sPreviousPrevio usPrevious <> " " And iCounter > 3 Then
Mid$(sString, iCounter - 1, 1) = "s"
End If
'FIX [ATF
If sCurrent = " " And sPrevious = "f" And sPreviousPrevio us = "t"
And sPreviousPrevio usPrevious = "A" Then
Mid$(sString, iCounter - 1, 1) = "F"
Mid$(sString, iCounter - 2, 1) = "T"
End If
'FIX (WA)
If sCurrent = ")" And sPrevious = "a" And sPreviousPrevio us = "W"
And sPreviousPrevio usPrevious = "(" Then
Mid$(sString, iCounter - 1, 1) = "A"
End If
'FIX INITIALS CONSONANTS PAIRS
If sCurrent = " " And sPreviousPrevio usPrevious = " " And sPrevious
<> "a" And sPrevious <> "e" And sPrevious <> "i" And sPrevious <> "o" And
sPrevious <> "u" And sPrevious <> " " And sPreviousPrevio us <> "A" And
sPreviousPrevio us <> "E" And sPreviousPrevio us <> "I" And sPreviousPrevio us
<> "O" And sPreviousPrevio us <> "U" And sPreviousPrevio us <> " " Then
Mid$(sString, iCounter - 1, 1) = UCase$(sPreviou s)
End If
'FIX INITIALS CONSONANTS PAIRS END
If sPreviousPrevio us = " " And sCurrent <> "a" And sCurrent <> "e"
And sCurrent <> "i" And sCurrent <> "o" And sCurrent <> "u" And sPrevious <>
"A" And sPrevious <> "E" And sPrevious <> "I" And sPrevious <> "O" And
sPrevious <> "U" And sPrevious <> " " And iCounter = iLength Then
Mid$(sString, iCounter, 1) = UCase$(sCurrent )
End If
'FIX RD
If sCurrent = "d" And sPrevious = "R" And sPreviousPrevio us = " "
And iCounter = iLength Then
Mid$(sString, iCounter, 1) = "o"
sString = sString & "ad"
End If
'FIX ST
If sCurrent = "t" And sPrevious = "S" And sPreviousPrevio us = " "
And iCounter = iLength Then
sString = sString & "reet"
End If
'FIX AVE
If sCurrent = "e" And sPrevious = "v" And sPreviousPrevio us = "A"
And sPreviousPrevio usPrevious = " " And iCounter = iLength Then
sString = sString & "nue"
End If
sPreviousPrevio usPrevious = sPreviousPrevio us
sPreviousPrevio us = sPrevious
sPrevious = sCurrent
Next iCounter
Proper = sString
End If
End Function
#########

Button with two fields to enter table name and field name.
#########
Private Sub ButtonCapitalis e_Click()
Dim sField As String
Dim sTable As String
Dim rs As Recordset
Dim sQuery As String
Dim dbCurrent As Database
Dim iCounter As Integer

sField = TextField.VALUE
sTable = TextTable.VALUE

sQuery = "SELECT * FROM " & sTable
Set dbCurrent = CurrentDb
Set rs = CurrentDb.OpenR ecordset(sQuery )
DoCmd.Hourglass True
With rs
rs.MoveFirst
Do Until .EOF 'Loop through the table.
rs.Edit
rs.Fields(sFiel d) = Trim(rs.Fields( sField))
rs.Fields(sFiel d) = Proper(rs.Field s(sField))
rs.Update
rs.MoveNext 'Move to the next record
iCounter = iCounter + 1
Loop
End With 'rsReg
DoCmd.Hourglass False
Set rs = Nothing
MsgBox "Capitalise d field " & sField & " in table " & sTable
End Sub
#########