Proper Case

  • Thread starter Thread starter Jaymes
  • Start date Start date
J

Jaymes

I want to convert a field to proper case. IE (LASTNAME or LASTNAME FIRST
NAME to Lastname or Last name First Name)

Is there a way to do this in a query with a formula.?

Thanks

(e-mail address removed)
 
You can use the StrConv function to change the first letter of every word to
upper case and lower case the rest:

UPDATE MyTable
SET MyField = StrConv([MyField], 3)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Access VBA built-in function:

StrConv("LAST NAME", vbProperCase) yields "Last Name"

You can use this in an Access query like this:

SELECT StrConv(LastNameColumn, 3) As LName, StrConv(FirstNameColumn,
3) As FName
FROM ...

vbProperCase = 3. You have to use 3 when using the StrConv() function
in a query instead of the Access instrinsic constant vbProperCase.

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDqIUYechKqOuFEgEQIXFgCg2MmK1hVFF+Gd2wvaimB3P6aqJgwAn3eu
leqYrIgWElwZvGdxh7s6+5uY
=zrOA
-----END PGP SIGNATURE-----
 
Jaymes

In addition to what John and MG have explained, I'll point out that the
StrConv() function is stupid!

What would you WANT to see from the following names:?

MCDONALD
VANDAMM
OBRIEN

Now, what do you suppose you WILL see?!
 
Hi Jaymes

You can use the following function. You can incorporate this in an Update
query to perform mass updates.

Best regards

Maurice St-Cyr
Micro Systems Consultants, Inc.



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.
'
' See Also: StrConv Function in the Microsoft Access 97 online Help.

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
 
Quite correct. I created a little function long ago that does a bit better
job. Please ignore the sloppy coding - I created this for Access 95! But
if it ain't broke, don't fix it. <s>
--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

==================================
Public Function ProperCase(strIn As String) As String
' This function works similar to StrConv vbProperCase,
' but it also recognizes *any* intervening punctuation
' and the common last name prefixes O', Mc, and Mac

Dim strWork As String, intP As Integer, intL As Integer, intCap As Integer
Dim strThisChar As String

' First letter is always cap
intCap = True

' Make a lower case copy of the string
strWork = LCase(strIn)

intP = 1
intL = Len(strWork)

Do While intP <= intL
strThisChar = Mid(strWork, intP, 1)
If intCap Then
Mid(strWork, intP, 1) = UCase(strThisChar)
' Special test for beginning "MAC", "MC", "O'", or "Van"
If Mid(strWork, intP, 3) = "Mac" Then
intP = intP + 2
ElseIf Mid(strWork, intP, 2) = "Mc" Then
intP = intP + 1
ElseIf Mid(strWork, intP, 2) = "O'" Then
intP = intP + 1
ElseIf Mid(strWork, intP, 3) = "Van" Then
intP = intP + 2
Else
intCap = False
End If
End If
' If this character is not a letter...
If Not (strThisChar Like "[a-z]") Then
' .. unless it's a single quote
If (strThisChar = "'") Or (Asc(strThisChar) = 146) Then
' Then next character should not be capitalized
intCap = False
Else
' Otherwise next character should be capitalized
intCap = True
End If
End If
intP = intP + 1
Loop

ProperCase = strWork

End Function
================================
 
Back
Top