Re: Concatenate with ORDER BY clause

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 wrote:
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

.

Relevant Pages

  • Re: Time a query
    ... Access MVP 2002-2005, 2007-2008 ... John Spencer wrote: ... You add a table to your database name named QueryResults with 4 fields: ... Dim dbAny As DAO.Database ...
    (microsoft.public.access.queries)
  • Re: check mark to filter data or not
    ... Dave Hargis, Microsoft Access MVP ... "John Spencer" wrote: ... ALL records when the checkbox on the form was false and only the true ... If check0 is false then return records where yesno fields are false or ...
    (microsoft.public.access.queries)
  • Re: Why Re-Link causes Corruption
    ... Doug Steele, Microsoft Access MVP ... "John Spencer" wrote: ... Try Allen Browne's article on Recovering from Corruption at: ... Oracle is not it, I've tried it on a db with no links to Oracle ...
    (microsoft.public.access.modulesdaovba)
  • Re: Multiple Cascading Combo Box
    ... Dave Hargis, Microsoft Access MVP ... "John Spencer" wrote: ...
    (microsoft.public.access.modulesdaovba)
  • Re: Show just one record each person-different results
    ... Access MVP 2002-2005, 2007 ... "John Spencer" wrote: ... Now that you know those three items, you can join the query on those ... items to the original table and return the highest Request ...
    (microsoft.public.access.queries)