Changing field data from uppercase 3

[THWatson]

The database with which I am working was imported from Q&A. All the entries in the LastName field are in upper case. I would like to convert them to proper case (eg. SIMPSON to Simpson).<br>I know I can make them appear that way with an Input Mask, but the underlying data will remain unchanged, so in reports or labels it will still come out SIMPSON.<br>I also know I can fix them by a search and replace method, but there are 700 entries.<br>Can anyone recommend a procedure?<br>Thanks.<br>Tom Watson (<A HREF="mailto:twatson@sentex.net">twatson@sentex.net</A>)

 

[DougP]

Create a function<br>like so I broke it down so you could see each step.<br><br>Public Function UpperLower(Name)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Dim Name1, LeftLetter, RightLetters As String<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;LeftLetter = Left(Name, 1) 'Gets the Upper case Left letter<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Name1 = LCase(Name)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'converts all letters to lower case<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;RightLetters = Right(Name, Len(Name) - 1) 'gets the right side Minus the first letter<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Name = LeftLetter & RightLetters&nbsp;&nbsp;&nbsp;'Concatenates the two half together<br>End Function <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[elizabeth]

Access has a built-in function for this you can use right in your query:<br>Expression1: StrConv([TableName]![Fieldname],3)<br><br>BTW, &quot;Help&quot; shows the second parameter as &quot;vbproper&quot;, but queries do not recognize vb constants, so you have to use the numeric equivalent.

 

[THWatson]

Elizabeth<br>Your suggestion was really helpful!<br>All I had to do was create an Update Query and update the records. It was quick and simple.<br>I had remembered seeing something in a Word 97 VBA book about Proper case, but couldn't find it in any Access 2000 manuals anywhere. I wonder why this is not documented, because I suspect it might be helpful to others.<br><br>In any event, your reply was much appreciated.<br>Tom Watson (<A HREF="mailto:twatson@sentex.net">twatson@sentex.net</A>)