any ideas????

[tommy_mo]

I was wondering if there is a way to separate information I have in a database. The database was originally designed with a field called "contacts"
At first only phone numbers were entered, but as time went on email addresses were added on separate lines eg:

123-345-6789
one@yahoo.com

There isn't any order to the field, so email address or phone could come first. There could also be multiple email and phone numbers entered.

What I would like to do is separate the phone numbers from the email addresses so that they have their own field. Is this possible? I've got about 15000 records and just the thought of doing it manually makes me ill. Cheers for any suggestions or thoughts. -Tom

 

[pbaldy]

If they're on separate lines, then there must be an <Enter> in there between each one. In VBA, you could use the Split function looking for vbCrLf to break out the entries into an array. Looping through that looking for "@" should help you distinguish the phone numbers from the emails.

 

[_Kev]

I'm a rookie, so I don't have an authorative answer, but I have some questions that may help you (or at least help you get more interest).

Is there a way to build a query that splits on the return/enter character (ASCII 10 or 13) using the Chr function?

Is there a way to build a query that will use some form of regular epression to search for the dashes in the phone or the @ in the email regardless of which comes first?

 

[Moniker]

Err, Kev, that's what baldy just suggested.

123-456-7890
email@place.com
987-654-3210
email2@place2.com
email3@place3.com

Splitting the above with a split function on chr(13) (the return character) would make each its own field.

Since you have 15,000 or so records, this is actually easier to properly convert with a little Excel programming.

 

[_Kev]

Yeah... I saw that after I posted. I guess I was just a little too anxious to help out.

 

[tommy_mo]

Thanks for the responses. I am interested in the Excel approach. Any more tips? I'll look into splitting the fields as baldy suggested. I'm sure I''l have a lot more questions! Cheers! -Tom

 

[tommy_mo]

OK. I've exported the db to Excel. I've got he column values as:

column1:
1232333||one@yahoo.com
two@yahoo.com||1234567||three@hayoo.com

I've tried the "text and columns" menu, but that only seems to seperate the first line of the column (eg: 1234567 and two@yahoo.com have been seperated into new columns) so that each <enter> entry is in it's own column? Any ideas? It must be something simple. -Tom

 

[Bat17]

What I would like to do is separate the phone numbers from the email addresses so that they have their own field. Is this possible? I've got about 15000 records and just the thought of doing it manually makes me ill. Cheers for any suggestions or thoughts. -Tom

You need to go 1 step further and seperate the tables not just the fields!
You need a table for contacts, another for email, joined to the contact table by the contact ID and the same for phone numbers.
You will want an additional field in the mail/phone tables to define the type of number ie, mobile or home or office.

Assuming your table is tblContacts with fields ContactID and Contact and that you create tblMail with ContactID and Eaddress, and tblPhone with ContactID and PhoneNumber, then this code should step through your data and sort it for you.

Make sure you test it out on a copy of the DB first though!!!

Sub SplitContacts()
Dim rs As DAO.Recordset
Dim rsMail As DAO.Recordset
Dim rsPhone As DAO.Recordset
Dim aStrIn() As String
Dim j As Integer

Set rs = CurrentDb.OpenRecordset("tblContacts")
Set rsPhone = CurrentDb.OpenRecordset("tblPhone")
Set rsMail = CurrentDb.OpenRecordset("tblMail")
Do While Not rs.EOF
    aStrIn = Split(rs!contact, vbCrLf)
    For j = 0 To UBound(aStrIn)
        If InStr(aStrIn(j), "@") Then
            With rsMail
                .AddNew
                !contactID = rs!contactID
                !Eaddress = aStrIn(j)
                .Update
            End With
        Else
            With rsPhone
                .AddNew
                !contactID = rs!contactID
                !PhoneNumber = aStrIn(j)
                .Update
            End With
        End If
    Next j
    rs.MoveNext
Loop
Set rs = Nothing
Set rsPhone = Nothing
Set rsMail = Nothing
End Sub

HTH

Peter

 

[tommy_mo]

Thanks for the help. I'll give it ashot and let you know what comes of it. It will have to go on the back burner for a while though, too many other things to work out! -Tom