Exporting Outlook 2000 Contacts to Access 2000

[RayAccess]

I am new to Access, so please help me in detail.
I have code written that takes data from Outlook contacts and puts it in a Table in Access but it puts all lines from "Street Address" in one column in a single line with small square seperating each line. I need to create a query that can seperete "Street Address" column into Address1, Address2, Address3 ( Where Address1, Address2 & Address3 should be first, second & third line from the street address field in outlook.
For e.g:
In Outlook Contact the address is divided as:
Street: 23-30 woodlane
Third floor
Suit 350
City: New York
State: NY

In Access it comes out as:

--- Street Address - - City - State

23-30 woodlane * Third floor * Suit 350 - New York - NY

* is Small square.
I want it as :

Address1--- Address2--- Address3----City-----State
23-30 woodlane - Third floor - Suit 350 - New york - NY

Please Hellllllllllp....
Thanks,

 

[jeremie_ingram]

So in basics, you want to take on line of delimited text from 1 field and break it into three seperate fields.

The trick here is the delimeter, being a text symbol for a retun. If you could post a small portion of the tbl containing that long string, it would really help me in assisting you. What you will be doing is creating a second tbl with all of the fields you need, then running the proper query between them to transfer the data into the appropriate fields.
If you can post that information here, I can work on it and get it to break it out properly for you. I ask this because I feel that this may require a bit more manipulation because of the delimeter.

 

[RayAccess]

Thanks for your reply.
Attached is the file, the "Street Address" field needs to be separated into Address1, Address2 & Address3.

Really appreciate your help....

 

[jeremie_ingram]

I ran into a few hitches trying to query the data out, so I made one select query that only grabbed the field with the symbol in it. I then opened the query and ran an export (FILE-EXPORT), set the file type to .txt and put it where you can easily find it (C:\ or desktop). Use the comma (default) for the delimiter and the " as text qualifier.

Open the text document and highlight one of the squares, right click and copy it. Now go to EDIT on the tool bar and select REPLACE. Paste the square into the FIND WHAT field and in the REPLACE WITH field put "," (include the quotes since they are text qualifiers). Once you click REPLACE ALL you will see it change. You will also note that there are still squares in the document. Dont worry about those. Go back into access and import this document into a new tbl and you will be able to have the fields seperated as you want.

Sorry for the round about way, but I couldnt quite get anything in access to recognize the carridge return symbol (the square).

 

[RayAccess]

I found a query that separates "Street Address" field but only in "Address2" it still shows small square in addresses with three lines. Attached is the file with that query, run it an you'll see it.
See if you can fix that.

Thanks for your time.

 

[Jon K]

I prefer using VBA code to nesting many IIF() functions in the update query. In this case, VBA is easier to debug.

You can open the form and click on the command button to update the fields Address1, Address2 and Address3.

The code is in the On Click event of the command button:-

Private Sub Command0_Click()
  Dim cnn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim SQL As String
  Dim Add1, Add2, Add3
    
  SQL = " SELECT [Street Address]," & _
        " Address1, Address2, Address3" & _
        " FROM tblLitContacts"
  
  Set cnn = CurrentProject.Connection
  Set rs = New ADODB.Recordset
       
  rs.Open SQL, cnn, adOpenKeyset, adLockOptimistic
  
  ' loop through the records.
  Do While Not rs.EOF
    Add1 = rs![Street Address]
    
    If InStr(Add1, Chr(10)) Then
      Add2 = Mid(Add1, InStr(Add1, Chr(10)) + 1)
      Add1 = Left(Add1, InStr(Add1, Chr(10)) - 1)
    End If
    
    If InStr(Add2, Chr(10)) Then
      Add3 = Mid(Add2, InStr(Add2, Chr(10)) + 1)
      Add2 = Left(Add2, InStr(Add2, Chr(10)) - 1)
    End If
    
    ' update record.
    rs!Address1 = Add1
    rs!Address2 = Add2
    rs!Address3 = Add3
    rs.MoveNext
    
    Add1 = Null
    Add2 = Null
    Add3 = Null
    
 Loop
 
 Set rs = Nothing
 Set cnn = Nothing
 
 MsgBox "Addresses updated."
End Sub