CONVERT FROM UPPER CASE TO Proper Case 1

[jflo]

All the data I feed my DB with is UPPERCASE. I need to convert this to Proper case but here's the trick

I'm in Quebec, Canada and some cities have names like "Saint-Anne-de-Bellevue". My question is, would it be better to use vba code for this or SQL could do it?

I have tried SQL and it doesn't simply do what I need

1-SELECT TLowerCase.Numéro,
2-UCase$(Left$([N],1))+Mid$([N],2) AS LastName,
3-IIf(InStr(1,[P],&quot;-&quot;,1)<>0,
4- (UCase(Left([P],1))
5- +Mid([P],2,(InStr(1,[P],&quot;-&quot;,1)-1))
6- +(IIf(InStr(InStr(1,[P],&quot;-&quot;,1),[P],&quot;-&quot;,1)<>0,
7- UCase(Mid([P],(InStr(1,[P],&quot;-&quot;,1)+1),1))
8- +Mid([P],(InStr(1,[P],&quot;-&quot;,1)+2),(InStr(InStr(1,[P],&quot;-&quot;,1),[P],&quot;-&quot;,1))+3)
9- +(IIf(InStr(InStr(1,[P],&quot;-&quot;,1),[P],&quot;-&quot;,1)<>0,
10- &quot;There's more&quot;,
11- UCase(Mid([P],(InStr(1,[P],&quot;-&quot;,1)+1),1))+Mid([P],(InStr(1,[P],&quot;-&quot;,1)+2))))))),
12- UCase(Left([P],1))+Mid([P],2)) AS Name
13-FROM TLowerCase;

The output of this example is --> &quot;Jean-François-There's more&quot; if the value in the table is &quot;Jean-François-Pierre&quot;
Of course, the output sould be &quot;Jean-François-Pierre&quot;. Somehow, the second &quot;InStr&quot; as the length in my mid fonction (line 8) doesn't return the intended value so I have to add 3 so it works for this example particularly.

The next step after that will be when you input --> &quot;SAINT-ANNE-DE-BELLEVUE&quot; to somehow figure out a way of having the output be &quot;Saint-Anne-de-Bellevue&quot;

The weirdest looking city name I saw is 7 word long with dash, so it would have to work for this name also.

I hope it will challenge some of you...

Thanks in advance

J-F

 

[tlbroadbent]

I recommend creating a VBA function. Someone provided one called PROPER for Access 97. It also works in Access 2000. I modified it slightly for your needs. It has a few shortcomings but should do most of what you want.

You can use it in an update query to change all of the names to proper case.

Update Table Set Name=Proper(Name)

The code for the function is...

Function Proper(var As Variant) As Variant
' Purpose: Convert the case of var so that the first letter of each word capitalized.

Dim strV As String, intChar As Integer, i As Integer
Dim fWasSpace As Integer 'Flag: was previous char a space?

If IsNull(var) Then Exit Function
strV = var
fWasSpace = True 'Initialize to capitalize first letter.
For i = 1 To Len(strV)
intChar = Asc(Mid$(strV, i, 1))
Select Case intChar
Case 65 To 90 ' A to Z
If Not fWasSpace Then Mid$(strV, i, 1) = Chr$(intChar Or &H20)
Case 97 To 122 ' a to z
If fWasSpace Then Mid$(strV, i, 1) = Chr$(intChar And &HDF)
End Select
fWasSpace = (intChar = 32 Or intChar = 45) ' check for space or -
Next
Proper = strV
End Function Terry

http://members.home.net/tlbroadbent/index.htm

 

[jflo]

What would be te right way to call this from the update query? I'm trying &quot;Expr1: (Proper([Champ3]))&quot; in the field field and I get a Access Jet engine blablabla.... If &quot;Champ3&quot; in the name of the field I need to proper-ized, how would I call the function? Thanks

 

[tlbroadbent]

Update Tbl Set Name=Proper(Name)
Where ... Terry

http://members.home.net/tlbroadbent/index.htm

 

[jflo]

Thanks a bunch. I was trying to do it from design mode and it kept putting &quot;&quot; around the field name. When you type it in SQL, it doesn't.

Just need to take care of our special set of characters like &quot;Ç&quot; which is ascii 128 and all the others.

 

[kgreen84084]

I have never used SQL for an update query but need to convert the case. Can you provide me any more specifics. I dont understand the syntax for the update Tbl set....
particularly the where function

 

[tlbroadbent]

With no Where clause, all records will be updated.

Update tbl Set col1=proper(col1)

The where clause in this query selects records where the first character is not already upper case.

Update tbl Set col1=proper(col1)
Where asc(left(col1,1))<>asc(ucase(left(col1,1)))

There is a standard function in access that handles most proper case conversions so you won't need to create the Prper function unless you need to customize it.

Update tbl Set col1=StrConv(col1,3)
Where asc(left(col1,1))<>asc(ucase(left(col1,1)))

You can add additional criteria to the where clause.

Update tbl Set col1=StrConv(col1,3)
Where asc(left(col1,1))<>asc(ucase(left(col1,1)))
And (col2=1 Or col3=&quot;USA&quot Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost