Access Data Manipulation Question 1

[fbacchus]

Hi:

I have an Access Database with a 'full name' field. Unfortunately, the actual name (the data) is sometimes, also appended with a ten digit phone number. Thus I have, in the name field, "full name phone". Phone is always a number. There is also another field called "phone" where the phone number should be placed.

I would like to extrapulate the phone number from the name field and place it in the "phone" field, thus leaving the "name" field only with the full name and the "phone" field with the phone number.

Any help how I can accomplish this, sample code would be appreciated. Thanks

fb

fb

 

[hneal98]

Here is a sample function you can use to do what you want.

Sub testfunct()
    Dim MyString As String
    Dim MyName As String
    Dim MyPhone As String
    Dim BreakLoc As Integer
    
    Dim i As Integer
    
    MyString = "John Smith7145555555"
    
    For i = 1 To Len(MyString)
    
        If IsNumeric(Mid(MyString, i, 1)) Then
        
            BreakLoc = i
            Exit For
            
        End If
    
    Next i
    
    MyName = Left(MyString, BreakLoc - 1)
    
    MyPhone = Mid(MyString, BreakLoc, 10)
    
    MsgBox MyName
    MsgBox MyPhone

End Sub

 

[fbacchus]

Thanks hneal98,

I copied your code and ran it and it worked great. Being new to this programming, how would I change this code to read the table containing the information. The table is called "User Analysis" and the field is called "Full_Name". I guess that the data will need to be updated in the Full_Name field and the "telephone" field. Would this be done in the same function ?

thanks

fb

fb

 

[PHV]

You may try this (SQL code):
UPDATE [User Analysis]
SET Full_Name = Left([Full_Name], Len([Full_Name])-10)
, telephone = Right([Full_Name], 10)
WHERE Full_Name Like '*##########'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hneal98]

Since you are new to programming, PHV's suggestion would probably be better since you are doing the updates at the query level instead of programmatically.

 

[fbacchus]

PHV and hneal98,

Thanks for the suggestions. The problem with trying to do this in a query is that the telephone number can have variable formats and does not have an exact starting place in the name field. By this I mean that the name field will have the person's last and first name, followed by one or more spaces then the phone number which can be 1234561234 or 123 456 1234 or 123-456-1234. So I would have to program for these conditions. I was thinking that I could create a file with the splitted information then use that file to update the specific record in User_Analysis (my table, based on the records' unique identifier. If it is possible to do the above in a query, then that would be great. What do you think ? Thanks.

fb

fb

 

[Ravenous1]

What is the original source of the data?

I've seen problems like this with data imported from a text source, where the import specs didn't appropriately account for how the fields were delimited in the text file...

 

[fbacchus]

Ravenous1:

I imported this into an access table from an excel spreadsheet. The data in the excel spreadsheet is represented in the same manner as the access file.

fb

fb

 

[Ravenous1]

I like the direction others have pointed you in. I think a hybrid of both Hneal98's and PHV's might work well for you:

Sub testfunct()
  Dim MyString As String
  Dim MyName As String
  Dim MyPhone As String
  Dim BreakLoc As Integer
  Dim rsFixFullName as DAO.Recordset
  Dim strSQL As String
  Dim i As Integer

  strSQL = "SELECT * FROM [User Analysis] As UA WHERE " _
         & "UA.[Full Name] LIKE '%[0-9]%'" 

  DBEngine(0).BeginTrans    
    
  Set rsFixFullName = CurrentDb.OpenRecordset(strSQL)

  With rsFixFullName

    If Not(.BOF And .EOF) Then
      .MoveFirst
      Do While Not .EOF
        MyString = .Fields("Full Name")
        For i = 1 To Len(MyString)
          If IsNumeric(Mid(MyString, i, 1)) Then
            BreakLoc = i
            Exit For
          End If
        Next i
    	.Edit
    	.Fields("Full Name") = Left(MyString, BreakLoc - 1)
    	.Fields("Phone") = Mid(MyString, BreakLoc)
        .Update
        .MoveNext
      Loop
    End If
    .Close
  End With
  Set rsFixFullName = Nothing
  DBEngine(0).CommitTrans
End Sub

 

[hneal98]

Good job Ravenous1. This is what fbacchus had requested. The next step is determining where to put the code.

fbacchus, do you have an idea for that?