By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,829 Members | 1,829 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,829 IT Pros & Developers. It's quick & easy.

'UPPER CASE' to 'Normal Writing'

P: n/a
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


Nov 12 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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" <an***********@yescomputer.com.au> wrote in message
news:40********@quokka.wn.com.au...
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

Nov 12 '05 #2

P: n/a
On Sat, 17 Jan 2004 00:08:05 +0800 in comp.databases.ms-access, "Andy
Chalkley" <an***********@yescomputer.com.au> wrote:
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


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

P: n/a
"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:pb********************************@4ax.com...

Not to mention Jr, Sr, III, etc.

Or how about:

"Right Reverend John Paul Jones III, DR., DDS."

You know him too?
He still owes me $50 from high school.
Nov 12 '05 #4

P: n/a
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" <an***********@yescomputer.com.au> wrote in message news:<40********@quokka.wn.com.au>...
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

Nov 12 '05 #5

P: n/a
On Sat, 17 Jan 2004 01:00:30 +0000 (UTC) in comp.databases.ms-access,
"Tom Travolta" <to*@travolta.com> wrote:
"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:pb********************************@4ax.com.. .

Not to mention Jr, Sr, III, etc.

Or how about:

"Right Reverend John Paul Jones III, DR., DDS."

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


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

P: n/a
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.OpenRecordset(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" <Ja*******@hotmail.com> wrote in message
news:19**************************@posting.google.c om...
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" <an***********@yescomputer.com.au> wrote in message

news:<40********@quokka.wn.com.au>...
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

Nov 12 '05 #7

P: n/a
Perhaps I should modify it to cope with McDribble and van der Wobble
Andy Chalkley

Nov 12 '05 #8

P: n/a
rkc

"Andy Chalkley" <an***********@yescomputer.com.au> wrote in message
news:40********@quokka.wn.com.au...
Perhaps I should modify it to cope with McDribble and van der Wobble
Andy Chalkley


Post back on how you implement that please.
Nov 12 '05 #9

P: n/a
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, sPreviousPrevious,
sPreviousPreviousPrevious, iCounter As Integer
Dim iLength As Integer

If IsNull(X) Then
Exit Function
Else
sString = CStr(LCase(X))
sPrevious = " "
sPreviousPrevious = ""
sPreviousPreviousPrevious = ""
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
sPreviousPrevious = "M" And sPreviousPreviousPrevious = " " Then
Mid$(sString, iCounter, 1) = UCase$(sCurrent)
End If
'FIX PO BOX
If sCurrent = " " And sPrevious = "o" And sPreviousPrevious = "P"
And iCounter = 3 Then
Mid$(sString, 2, 1) = "O"
End If
'FIX NT
If sCurrent = "t" And sPrevious = "N" And sPreviousPrevious = " "
And iCounter = iLength Then
Mid$(sString, iCounter, 1) = "T"
End If
'FIX NSW
If sCurrent = "w" And sPrevious = "s" And sPreviousPrevious = "N"
And sPreviousPreviousPrevious = " " 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 sPreviousPrevious = "Q"
And sPreviousPreviousPrevious = " " 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 sPreviousPrevious = " "
And iCounter = iLength Then
Mid$(sString, iCounter, 1) = "A"
End If
'FIX WA
If sCurrent = "a" And sPrevious = "W" And sPreviousPrevious = " "
And iCounter = iLength Then
Mid$(sString, iCounter, 1) = "A"
End If
'FIX Smith'S
If sCurrent = " " And sPrevious = "S" And sPreviousPrevious = "'"
And sPreviousPreviousPrevious <> " " And iCounter > 3 Then
Mid$(sString, iCounter - 1, 1) = "s"
End If
'FIX [ATF
If sCurrent = " " And sPrevious = "f" And sPreviousPrevious = "t"
And sPreviousPreviousPrevious = "A" Then
Mid$(sString, iCounter - 1, 1) = "F"
Mid$(sString, iCounter - 2, 1) = "T"
End If
'FIX (WA)
If sCurrent = ")" And sPrevious = "a" And sPreviousPrevious = "W"
And sPreviousPreviousPrevious = "(" Then
Mid$(sString, iCounter - 1, 1) = "A"
End If
'FIX INITIALS CONSONANTS PAIRS
If sCurrent = " " And sPreviousPreviousPrevious = " " And sPrevious
<> "a" And sPrevious <> "e" And sPrevious <> "i" And sPrevious <> "o" And
sPrevious <> "u" And sPrevious <> " " And sPreviousPrevious <> "A" And
sPreviousPrevious <> "E" And sPreviousPrevious <> "I" And sPreviousPrevious
<> "O" And sPreviousPrevious <> "U" And sPreviousPrevious <> " " Then
Mid$(sString, iCounter - 1, 1) = UCase$(sPrevious)
End If
'FIX INITIALS CONSONANTS PAIRS END
If sPreviousPrevious = " " 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 sPreviousPrevious = " "
And iCounter = iLength Then
Mid$(sString, iCounter, 1) = "o"
sString = sString & "ad"
End If
'FIX ST
If sCurrent = "t" And sPrevious = "S" And sPreviousPrevious = " "
And iCounter = iLength Then
sString = sString & "reet"
End If
'FIX AVE
If sCurrent = "e" And sPrevious = "v" And sPreviousPrevious = "A"
And sPreviousPreviousPrevious = " " And iCounter = iLength Then
sString = sString & "nue"
End If
sPreviousPreviousPrevious = sPreviousPrevious
sPreviousPrevious = sPrevious
sPrevious = sCurrent
Next iCounter
Proper = sString
End If
End Function
#########

Button with two fields to enter table name and field name.
#########
Private Sub ButtonCapitalise_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.OpenRecordset(sQuery)
DoCmd.Hourglass True
With rs
rs.MoveFirst
Do Until .EOF 'Loop through the table.
rs.Edit
rs.Fields(sField) = Trim(rs.Fields(sField))
rs.Fields(sField) = Proper(rs.Fields(sField))
rs.Update
rs.MoveNext 'Move to the next record
iCounter = iCounter + 1
Loop
End With 'rsReg
DoCmd.Hourglass False
Set rs = Nothing
MsgBox "Capitalised field " & sField & " in table " & sTable
End Sub
#########
Nov 12 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.