Concatenate with ORDER BY clause

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BillA - 30 May 2008 15:05 GMT

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

Reply to this Message

John Spencer - 30 May 2008 17:21 GMT

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
[quoted text clipped - 12 lines]
> Thank you,
> Bill

Reply to this Message

BillA - 30 May 2008 18:19 GMT

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?
>
[quoted text clipped - 30 lines]
> > Thank you,
> > Bill

Reply to this Message