Concatenate with ORDER BY clause

[BillA]

I am using Duane Hookom’s concatenate function and am having difficulty
including an ORDER BY clause in its select statement. Below is the SQL that
I've been beating my head against. Everything works as expected, however I
am trying to order the concatenated field in Descending order by 'IssueDate.'
Any suggestions?

SELECT T1.ProjectID, T1.RenewAmendID, T1.DateReceived,
Concatenate("Select IssueDate & ': ' & Issue FROM tbl_RenewAmendComments
WHERE RenewAmendID =" & [RenewAmendID],Chr(13) & Chr(10)) AS Issues
FROM tbl_RenewORAmend AS T1
WHERE (((T1.DateReceived)=(SELECT MAX(T2.DateReceived)
FROM tbl_RenewORAmend as T2
WHERE T2.ProjectID = T1.ProjectID)))

Thank you,
Bill

 

[John Spencer]

Have you tried the following?

SELECT T1.ProjectID, T1.RenewAmendID, T1.DateReceived,

Concatenate("Select IssueDate & ': ' & Issue FROM tbl_RenewAmendComments
WHERE RenewAmendID =" & [RenewAmendID] & " ORDER BY IssueDate DESC",Chr(13) &
Chr(10)) AS Issues

FROM tbl_RenewORAmend AS T1
WHERE (((T1.DateReceived)=(SELECT MAX(T2.DateReceived)
FROM tbl_RenewORAmend as T2
WHERE T2.ProjectID = T1.ProjectID)))


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[BillA]

John,

That did the trick - thanks once again for your help. After looking at my
previous attempts, I note that my quotes were incorrect.

Take care,
Bill
____________________________

Have you tried the following?

SELECT T1.ProjectID, T1.RenewAmendID, T1.DateReceived,

Concatenate("Select IssueDate & ': ' & Issue FROM tbl_RenewAmendComments
WHERE RenewAmendID =" & [RenewAmendID] & " ORDER BY IssueDate DESC",Chr(13) &
Chr(10)) AS Issues

FROM tbl_RenewORAmend AS T1
WHERE (((T1.DateReceived)=(SELECT MAX(T2.DateReceived)
FROM tbl_RenewORAmend as T2
WHERE T2.ProjectID = T1.ProjectID)))


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

I am using Duane Hookom’s concatenate function and am having difficulty
including an ORDER BY clause in its select statement. Below is the SQL that
I've been beating my head against. Everything works as expected, however I
am trying to order the concatenated field in Descending order by 'IssueDate.'
Any suggestions?

SELECT T1.ProjectID, T1.RenewAmendID, T1.DateReceived,
Concatenate("Select IssueDate & ': ' & Issue FROM tbl_RenewAmendComments
WHERE RenewAmendID =" & [RenewAmendID],Chr(13) & Chr(10)) AS Issues
FROM tbl_RenewORAmend AS T1
WHERE (((T1.DateReceived)=(SELECT MAX(T2.DateReceived)
FROM tbl_RenewORAmend as T2
WHERE T2.ProjectID = T1.ProjectID)))

Thank you,
Bill