Trimming a string and updating a field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database that contains names and addresses which I need to clean.
Some records in the first name field have middle initials and some don't.
The middle initial is separated from the name by a space (eg. SMITH R). I
need to remove this middle initial. The code I have (below) doesn't seem to
work and makes Access crash. Any suggestions on what I'm doing wrong?

The database has about 320,000 records. I'm using Access 97.

Private Sub cmdRemoveMiddleInitial_Click()

Dim dbs As Database, rstElectoral As Recordset, position As Long,
firstName As Field, newName As String, name As String

Set dbs = CurrentDb
Set rstElectoral = dbs.OpenRecordset("Electoral_register_CFA")
Set firstName = rstElectoral.Fields("First_Name")

'Work through records and if a name has a middle initial trim the string
to remove middle initial.
Do While Not rstElectoral.EOF
If firstName Like "* ?" Then
rstElectoral.Edit
name = firstName
position = InStr(name, " ") 'Get position of space
newName = Left(name, position) 'Use position of space to trim name
firstName = newName
rstElectoral.Update
End If
Loop

End Sub
 
Not sure why it might "crash", but your code never tells ACCESS to move to
the next record in the rstElectoral recordset. Add a MoveNext step.

Do While Not rstElectoral.EOF
If firstName Like "* ?" Then
rstElectoral.Edit
name = firstName
position = InStr(name, " ") 'Get position of space
newName = Left(name, position) 'Use position of space to trim
name
firstName = newName
rstElectoral.Update
End If
' new step added below
rstElectoral.MoveNext
Loop
 
Back
Top