Web Development

General discussion


Case Conversion Access 2000

By MRA2 ·
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???

Alan AbramowitzA@coned.com

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by techrep In reply to Case Conversion Access 20 ...

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
'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

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

Collapse -

by MRA2 In reply to

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?


Collapse -

by MRA2 In reply to Case Conversion Access 20 ...

This question was closed by the author

Related Discussions

Related Forums