How to Eliminate "rogue" line-break character 1

[Flo79]

Hello,


Here is a problem that has truly been driving me crazy:



I am using vba code to concantenate records.

Each record in the concantenated string is seperated from the next by the beginning of a new line.
I use the following simple code to do this:
[My String] & vbNewLine

The concantenated records apear perfectly in my query.

However, when exporting the query results to MS Exel (Which is my needed goal) there is a rogue, square-looking character at the end of each line in the cells containing my concantenation results.

Does Anyone know how to get rid of these "Squares" within the vba code itself.

Below is the complete code I used (originally from Dev Ashish):

-----------------------------------------------------------


'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) _
As String
'Returns a field from the Many table of a 1:M relationship
'in a semi-colon separated format.
'
'Usage Examples:
' ?fConcatChild("Order Details", "OrderID", "Quantity", _
"Long", 10255)
'Where Order Details = Many side table
' OrderID = Primary Key of One side table
' Quantity = Field name to concatenate
' Long = DataType of Primary Key of One Side Table
' 10255 = Value on which return concatenated Quantity
'
Dim db As Database
Dim rs As Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String
On Error GoTo Err_fConcatChild

varConcat = Null
Set db = CurrentDb
strSQL = "Select [" & strFldConcat & "] From [" & strChildTable & "]"
strSQL = strSQL & " Where "

Select Case strIDType
Case "String":
strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue & "'"
Case "Long", "Integer", "Double": 'AutoNumber is Type Long
strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
Case Else
GoTo Err_fConcatChild
End Select

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Are we sure that 'sub' records exist
With rs
If .RecordCount <> 0 Then
'start concatenating records
Do While Not rs.EOF
varConcat = varConcat & rs(strFldConcat) & vbNewLine
.MoveNext
Loop
End If
End With

'That's it... you should have a concatenated string now
'Just Trim the trailing ;
fConcatChild = Left(varConcat, Len(varConcat) - 2)

Exit_fConcatChild:
Set rs = Nothing: Set db = Nothing
Exit Function
Err_fConcatChild:
Resume Exit_fConcatChild

END_Function:

End Function

__________________________________________

Many Thanks in Advance!

 

[SkipVought]

Hi,

it merely a metter of FORMATTING in Excel in the cells contrining the LF character.

[TheExcelRange].WrapText = True

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[Flo79]

Dear SkipVought,

I tried that. Still a quare before each line break.

Thanks you nevertheless.

Cheers!

 

[SkipVought]

then use vbLF instead of vbNewLine

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[Flo79]

Thank You Skip! That did the trick. Never heard of vblf before.

Thanks again!

 

[IrishJim]

I've used vbCrLf successfully.
I expect it puts in the traditional pair of codes Carriage Return and LineFeed, (hex 0d and 0a, decimals 13 and 10), which most text editors put at the end of each line.

 

[SkipVought]

CrLf is a throwback to character printer carriage controls.

A Line Feed would ONLY advance the platten one line's worth of a rotation.

A carriage return returned the print head back to the left-most position.

BOTH were needed to create a new line that was left-justified.

vbNewLine and vbCr are BOTH identical and do not give the desired display result.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}