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" <Jaeymeson@hotmail.com> wrote in message
news:191bb0e6.0401161715.659dbd22@posting.google.c om...[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]
