How to insert "Manual Line Break" in querry results.

[Mario]

I have a querry that has the following two columns:
1. StudentID
2. StudentDetails

The column StudentDetails invokes a VBA
function "Concatenate" to obtain the results from two
tables based on StudentID.

Sample Result:

StudentID StudentDetails
1 StudentID:1, NoOfSubjects:2, StudentID--1-
-SubjectID--1--64, StudentID--1--SubjectID--2--85

Desired result:

StudentID StudentDetails
1 StudentID:1,
NoOfSubjects:5,
StudentID--1--SubjectID--1--64
StudentID--1--SubjectID--2--85

Is there any way to insert a special character (like ^I
in MS-WORD) in the querry results (i.e mean inside the
VBA function), so that when I export the query results to
MS-Excel or MS-Word the results appear formatted.

Pls help.

Thanks

 

[Duane Hookom]

If you are using the concatenate function that I wrote, the second function
argument is the delimiter. You can set this to Chr(13) & Chr(10).
Concatenate("your select statement...", Chr(13) & Chr(10))
You can also use Chr(9) between field in your select statement.
Concatenate("select FirstName & Chr(9) & LastName FROM...", Chr(13) &
Chr(10))

 

[Guest]

Thanks. Yes, I am using the same function that you had
posted to one of my questions a few weeks/months in this
forum.

The solution you had said works fine when I export the
query results to MS-Word (or using the option "Publish
with MS-WORD").

When I export the query results to MS-Excel ("Analyze
with Excel"). The special characters wont show up. Any
work arounds for it in the excel. I am aware that
Carraige return with in a cell is equivalen to Alt+Enter.

Pls help.

Thanks
Mario

 

[Duane Hookom]

Have you tried
Chr(13)
Chr(10)
Chr(10) & Chr(13)
Chr(13) & Chr(10)