I have a large table with phone numbers listed as say "modem:08634456"
I need to update all of these to "modem:003538634456".
I have code to strip off the modem:0 from each entry but how do I now add on "modem:00353" to each record?? below is my bit of code. Also the code just stops when it hits a record that has a null value for phone. How can I get it to skip over the null value to the next entry??
Private Sub Command0_Click()
Dim DB As Database
Dim y As Integer
Set DB = CurrentDb()
Dim RS As DAO.Recordset
Dim mystring As String
Set RS = DB.OpenRecordset("machines", dbOpenTable)
Do While Not RS.EOF
If RS("Phone") <> Null Then
For y = 1 To Len(RS("phone")) Step 1
If Mid(RS("phone"), y, 1) = ":" Then
RS("phone") = Right(RS("phone"), Len(RS("phone")) - y)
If Mid(RS("phone"), 1, 1) = "0" Then
RS("phone") = Right(RS("phone"), Len(RS("phone")) - 1)