Re: Sorting Crosstab after Format of Date

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Is there a reason you couldn't use:
Format([recDate],"yy-mm") AS MyDate


--
Duane Hookom
MS Access MVP
--

"Steven M. Britton" <StevenMBritton@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:8456E63E-54F0-4591-BA13-97D8C9E8CD23@xxxxxxxxxxxxxxxx
>I am trying to get a Crosstab query to work properly. I can data to show
>but
> it is sorting
>
> Apr-04 Apr-05 Aug-04 Dec-04 Dec-05
>
> I realize that when I formatted the date in the test001 query that it
> becomes a string, but I left in the original date as my sorting option.
> However I can't get it to sort the column heading in true date sort. Help
> please.
>
> This is the cross tab query:
>
> TRANSFORM Sum(test001.Hours) AS SumOfHours
> SELECT test001.ProjectID, test001.Description, test001.Role,
> Sum(test001.Hours) AS [Total Of Hours]
> FROM test001
> GROUP BY test001.ProjectID, test001.Description, test001.Role,
> test001.MyDateSort
> ORDER BY test001.MyDateSort
> PIVOT test001.MyDate;
>
> This is the Test001 Query:
>
> SELECT Project_Tracking.ProjectID, Project_ID.Description, PERSONEL.Role,
> Project_Tracking.Hours, Project_Tracking.recDate AS MyDateSort,
> Format([recDate],"mmm") & "-" & Format([recDate],"yy") AS MyDate
> FROM PERSONEL INNER JOIN (Project_ID INNER JOIN Project_Tracking ON
> Project_ID.ProjectID = Project_Tracking.ProjectID) ON PERSONEL.Initials =
> Project_Tracking.Initials
> GROUP BY Project_Tracking.ProjectID, Project_ID.Description,
> PERSONEL.Role,
> Project_Tracking.Hours, Project_Tracking.recDate, Format([recDate],"mmm")
> &
> "-" & Format([recDate],"yy")
> ORDER BY Project_Tracking.recDate;
>


.

Relevant Pages

  • Re: Make Table Query - Sorting Errors
    ... Dim OutputTable As DAO.Recordset ... The query to concatenate the CAMPNO values will need to be LOOKING at the ... If you post the query you are using to do this, I beleive we can get it to ... Is there a command that I can use in the module code to first sort the ...
    (microsoft.public.access.queries)
  • Re: Make Table Query - Sorting Errors
    ... You sort in a query, ... You can do a compound sort in a query. ... "Tom Ellison" wrote: ... resulting table only has one record for each value of "CAMPNO". ...
    (microsoft.public.access.queries)
  • Re: Query doesnt always sort
    ... You have to use a query to sort the data in to your required order and then ... Access MVP 2002-2005, 2007 ... University of Maryland Baltimore County ... How do I impose a sort on the datasheet view? ...
    (microsoft.public.access.queries)
  • Re: Make Table Query - Sorting Errors
    ... MS Access MVP ... resulting table only has one record for each value of "CAMPNO". ... Is there a command that I can use in the module code to first sort the ... I have a make table query that is supposed to sort in ascending order ...
    (microsoft.public.access.queries)
  • Re: Make Table Query - Sorting Errors
    ... sorted, it will sort them as they arrive. ... you query them, why is this of any concern to you? ... correctly on the field CAMPNO. ... "Duane Hookom" wrote: ...
    (microsoft.public.access.queries)