Converting LowerCase to UpperCase

[ChrisQuick]

We have a table in Access 97 that all of the data was entered in lowercase. We need that data in all caps. Is there an easy way to convert it?<br>
<br>
Thanks

 

[DougP]

look at UCASE<br>
X = UCASE(Mytext)<br>
you can also turn on &quot;format&quot; for the field in the Table Design view use a Greater than sign &quot;&gt;&quot; to do this.<br>
also you probablay want to ensure that the data is entered from now on in CAP's by adding an &quot;Imput Mask&quot; to the field either in the Table or on a form.<br>
Use the same Greater than symbol there too.<br>
<br>
<br>

 

[elizabeth]

Good catch, DougP! Nothing like answering the question that would/should come next.

 

[ag]

What about if your table has 255 fields or something horrible like that? Writing the query or VB code to do that is not pleasant. I have knocked up a noddy that iterates through an Access table (not an attached one though the code change is one constant to do this) and sets all the field values for you.<br>
<br>
Paste the attached code into a module and change the tablename and conversion function (commented).<br>
<br>
HTH<br>
<br>
Andrew<br>
<br>
<br>
-----------<br>
Public Sub ConvertIcase()<br>
<br>
Dim db As Database<br>
Dim r As Recordset<br>
Dim f As Field<br>
<br>
Dim strTablename As String<br>
<br>
strTablename = &quot;InsertYourTableNameHere&quot;<br>
<br>
Set db = CurrentDb()<br>
Set r = db.OpenRecordset(strTablename, dbOpenTable) 'change locks etc as required<br>
<br>
With r<br>
If Not .BOF Then<br>
.MoveFirst<br>
<br>
Do While Not .EOF<br>
.Edit<br>
For Each f In r.Fields<br>
f = UCase(f) 'lcase/ucase anything you like really<br>
Next<br>
.Update<br>
.MoveNext<br>
Loop<br>
End If<br>
.Close<br>
End With<br>
<br>
Set f = Nothing<br>
Set r = Nothing<br>
Set db = Nothing<br>
<br>
End Sub<br>
------------<br>

 

[ag]

Apologies for the layout of the code in the previous post....the server seems to strip out tab stops.<br>
<br>
Can email if required.<br>
<br>
Andrew<br>

 

[David]

Saw your frustration on the tab thingy...<br>
<br>
If you put the word tab in small case closed by [] you will get the tabbing. One of these gets about 3 characters, two gets you 6 and so on.<br>
<br>
Post here if this is not clear. I've got this thread marked.<br>
<br>
Dave

 

[NormDuffy]

Before converting the data to upper case or using masks to do it always ask the question. What if I want it in the correct case afterwards. It is easy to display the data in uppercase or lower case but extremely difficult to fix case problems at a later date. A little piece of code that i use often when users fall into this trap follows
Hope this helps
Norm
Function Proper(varAny As Variant) As Variant
' Converts first letter of each word to uppercase
' Usefull for converting surnames to correct case.
' Note: although this function converts most proper names correctly, it converts
' &quot;McKee&quot; to &quot;Mckee&quot;, &quot;van Buren&quot; to &quot;Van Buren&quot;, &quot;John III&quot; to &quot;John Iii&quot;
' Maybe worthwhile to create a table of known errors and use an update query to
' fix the conversion errors. This would be built up over a period of time.
' Some may not be anticipated. For example Comm'Ty

Dim intPtr As Integer
Dim strName As String
Dim strCurrChar As String, strPrevChar As String

If IsNull(varAny) Then
Exit Function
End If
strName = CStr(varAny)
For intPtr = 1 To Len(strName) ' Go through string char by char.
strCurrChar = Mid$(strName, intPtr, 1) ' Get the current character.

' If previous char is letter, this char should be lowercase.
Select Case strPrevChar
Case &quot;A&quot; To &quot;Z&quot;, &quot;a&quot; To &quot;z&quot;
Mid(strName, intPtr, 1) = LCase(strCurrChar)
Case Else
' the If statement added to correct error of conversin
' when &quot;'&quot; is the second last character of the string or
' when &quot;'s&quot; is at the end of the word eg &quot;Women's Hospital&quot;
If (intPtr = Len(strName) Or Mid(strName, intPtr, 1) = &quot;s&quot And strPrevChar = &quot;'&quot; Then
Mid(strName, intPtr, 1) = LCase(strCurrChar)
Else
Mid(strName, intPtr, 1) = UCase(strCurrChar)
End If
End Select

strPrevChar = strCurrChar
Next intPtr
Proper = CVar(strName)
End Function