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


This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
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


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

Back to Web Development Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums