Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Sep 2004
    Posts
    113

    Unanswered: how do i get rid of 2 line returns in each cell to 1 line

    hey guys. I recently did a data export from my access database. How do i get rid of 2 line returns in each cell so it's all on one line. It looks like there is a tab in a stream of text such as address. In access there is a little box inbetween each return. When i export as a .xls there is a bold pipe looking symbol betwen each line. here are some examples.

    "350 S. Grand Ave.
    43rd Floor"

    "8301 Florence Ave.
    #318"

    how do i get those like this "350 S. Grand Ave. 43rd Floor"
    "8301 Florence Ave. #318".
    Theres about 700 and it's way too time consuming to go to each one and press back space. What is the most efficient way to hadle this?? Thanks guys
    Last edited by inho78; 10-20-04 at 16:27. Reason: wronf title

  2. #2
    Join Date
    Mar 2004
    Posts
    42
    Are you exporting your data from a query? If you do, do this in your query: Address: [Address1] & " " & [Address2]. This will concatenate your two fields into one.

    HTH,

    ESM

  3. #3
    Join Date
    Sep 2004
    Posts
    113
    look at bottom
    Last edited by inho78; 10-20-04 at 18:30.

  4. #4
    Join Date
    Sep 2004
    Posts
    113
    i am exporting the file out of a query. However, the address is already concatenated. What I want to do is have 1 line address in each cell. Under the address column the data looks like this after I export.
    350 S. Grand Ave.
    43rd Floor

    I want the data in the cell to look like this:
    350 S. Grand Ave. 43rd Floor

    The problem lies that there are about 700 records like this and in order to do it manually I have to place the cursor between the street and the suite or floor # and press backspace. When i view the cell in access before i do any of this there is a strange looking box character which represents either a tab or a line feed to my knowledge.

    Thank you

  5. #5
    Join Date
    Sep 2004
    Posts
    113
    bump.............

  6. #6
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    inho78 -

    Put this code in a Module, replace table and field with the name of your table and field respectively, and run it. The line feed characters will be replaced with " " (space). Of course, make a backup of your table before running this, just to be safe...

    Code:
    Public Sub Replace_LineFeed()
        Dim rst As ADODB.Recordset
        
        Set rst = New ADODB.Recordset
        With rst
            .Open "SELECT field FROM table;", CurrentProject.Connection, & _
                adOpenKeyset, adLockOptimistic
            .MoveFirst
            Do Until .EOF
                .Fields("field") = VBA.Replace(.Fields("field"), vbCrLf, " ")
                .Update
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
    
    End Sub
    Last edited by BummerJeff; 10-20-04 at 23:06.

  7. #7
    Join Date
    Sep 2004
    Posts
    113

    Code

    thanks alot but when i run the code i get an error. is there something wrong with the syntax..please help. The error is "compile error, expected AS" Also do i just add this in a new module and run it than save it and run the sql query back in degin mode?

    Public Sub Replace_LineFeed()
    Dim rst As ADODB.Recordset

    Set rst = New ADODB.Recordset
    With rst
    .Open "SELECT AADDRESS FROM PEOPLE;", CurrentProject.Connection, & _
    adOpenKeyset, adLockOptimistic
    .MoveFirst
    Do Until .EOF
    .Fields("AADDRESS") = VBA.Replace(.Fields("AADDRESS"), vbCrLf, " ")
    .Update
    .MoveNext
    Loop
    .Close
    End With
    Set rst = Nothing

    End Sub

  8. #8
    Join Date
    Sep 2004
    Posts
    113
    bump........

  9. #9
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    I'm sorry, I made a slight mistake in the code. Get rid of the & after the CurrentProject.Connection part. So this:

    .Open "SELECT AADDRESS FROM PEOPLE;", CurrentProject.Connection, & _

    Should become

    .Open "SELECT AADDRESS FROM PEOPLE;", CurrentProject.Connection, _

    It should compile correctly then.

    Quote Originally Posted by inho78
    Also do i just add this in a new module and run it than save it and run the sql query back in degin mode?
    All you need to do is put this in a blank module (not a form or class module), place the cursor inside the procedure, and press F5. Nothing else needs to be done.

  10. #10
    Join Date
    Sep 2004
    Posts
    113
    it ran this time but now the error is "invalid use of null"
    is this cuz i have empty cells?

  11. #11
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    Probably. Try this:

    Code:
    Public Sub Replace_LineFeed()
        Dim rst As ADODB.Recordset
        
        Set rst = New ADODB.Recordset
        With rst
            .Open "SELECT field FROM table;", CurrentProject.Connection, _
                adOpenKeyset, adLockOptimistic
            .MoveFirst
            Do Until .EOF
                If Not VBA.IsNull(.Fields("field")) Then
                    .Fields("field") = VBA.Replace(.Fields("field"), vbCrLf, " ")
                    .Update
                End If
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
    
    End Sub

  12. #12
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    Sorry, duplicate post.
    Last edited by BummerJeff; 10-21-04 at 17:13.

  13. #13
    Join Date
    Sep 2004
    Posts
    113
    the code RAN!
    However, how do I view the result that were updated to the table? I went back into deisgn mode in access and clicked on the table but the cells were not updated. thanks you so mnuch

  14. #14
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    Close the table and anything that references it (query/form), run the code, then open the table. The field should be updated, unless you're not using the right field and table names...

  15. #15
    Join Date
    Sep 2004
    Posts
    113
    i ran the code and opened the table.
    the cell still looks like this
    AADDRESS
    "6053 West Century Blvd Suite 1100"
    but between blvd and suite there is a little square. does this sqaure represent a retun feed(vbcrlf). If i export this to excel it is a bold looking pipe symbol. thank you've been a tremendous hlpe

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •