Combine address lines in a report

[CHTHOMAS]

Hi,

I have an address field on Report. The users pressed "Enter Keys" while entering address in form. Presently the address appears on a text box in different lines in the report. Is there any way to make the address to appear as a single line in text box? I.e. combine address line1, address line2.etc into a single line in thetext box. Any help will be appreciated.

Regards

Charley

 

[raskew]

Assuming the text-box is bound to a field in a table, you could use the following to update the table:

Function stripit(ptblName As String, pfldName As String)
'*******************************************
'Name: stripit (Function)
'Purpose: Search and replace CR-LF with " "
'Inputs: ? stripit("table1", "MyAddress&quot
'*******************************************

Dim db As Database, rs As Recordset, k As Integer
Dim NL As String, strHold As String, j As Integer
Dim strSQL As String, n As Integer, x As Integer

NL = Chr(13) & Chr(10) ' Define newline.
strSQL = "SELECT " & pfldName & " FROM " & ptblName & "" _
& " WHERE (((InStr(" & pfldName & ", '" & NL & "'))>0));"
Debug.Print strSQL
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
rs.MoveLast
rs.MoveFirst
n = rs.RecordCount
j = Len(NL)

For x = 1 To n
strHold = RTrim(rs(pfldName))
Do While InStr(strHold, NL) > 0
k = InStr(strHold, NL)
strHold = Left(strHold, k - 1) & " " & Mid(strHold, k + j)
Loop
Debug.Print strHold

'update the recordset
rs.Edit
rs(pfldName) = strHold
rs.Update

'move to next record
rs.MoveNext
Next x

rs.Close
db.Close
Set db = Nothing
End Function

 

[Thanks]

Thanks. Let me try.

 

[RickSpr]

If you don't want to combine the lines in the database, just in the report, change the report's text box Control Source property to "=LinearAddress([Address])" (using your own address field name, of course). Then add the following function to a standard module.

    Public Function LinearAddress(Addr As String) As String
        Dim i As Integer, result As String
        result = Addr
        i = 1
        Do
            i = Instr(i, result, vbCrLf)
            If i = 0 Then Exit Do
            result = Left$(result, i - 1) & " " & Mid$(result, i + 2)
        Loop
        LinearAddress = result
    End Function

NOTE: If the text box has the same name as the table field, you will get "#Error" in it, because VBA will think that "[Address]" refers to the control instead of the field, which would be a circular reference. All you have to do is name the text box something else. Rick Sprague