Web Development

Ask something
Discussion Locked

Case Conversion Access 2000

0 helpful
3 replies

by mra2

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I took over an existing database and now all NEW RECORDS have Proper Case (John J Smith).

However, all the ALREADY existing records show up in all kinds of cases in the TABLE. ALL UPPER, ALL LOWER and various combinations of both.

I need a VBA for Access code for reading each Name field, converting to Proper Case, and saving it IN THE TABLE as proper case.

Can anyone help???

Thanks,
Alan AbramowitzA@coned.com

3 replies

by techrep

Use a function like the FirstCap function below:

Function Fix_The_Names()
'Code to convert all names to proper case

Dim Db as Database, Rs as Recordset, TableName as String

TableName = "yourtablename goes here"

Set Db = CurrentDb
Set Rs = Db.OpenRecordset(TableName)
Do While Not Rs.Eof
Rs.Edit
'use your fieldnames in the following code:
If Not Isnull(Rs!LastName) Then
Rs!LastName = FirstCap(Rs!LastName)
End If
If Not Isnull(Rs!FirstName) Then
Rs!LastName = FirstCap(Rs!LastName)
End If
'etc...
Rs.Update

Rs.Movenext
Loop
Msgbox "All done"

End Function

Function FirstCap(Instring As String) As String
'capitalizes the first letter of a string and makes the rest lower case
Dim buf As String

Instring = Trim(Instring)
If Len(Instring) = 0 Then
Exit Function
End If

Instring = LCase(Instring)
Mid$(Instring, 1, 1) = UCase$(Left$(Instring, 1))
FirstCap = Instring
End Function

by mra2

Dear Techrep@venturehosts.com,

Do I put your functions in a General Module or a Form Module?

How do I get the funcyion to execute?

Thanks!!!!!!!!!!!!!!!!!!
Alan

This discussion is closed to new replies.