Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Location
    Virginia
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom formatted date in ctab query (A2K)

    Rows and column headings are date fields formatted "mm/yyyy" in the crosstab query. Spans two separate years.

    How to force row and column headings to sort chronologically not alpha in the crosstab query?

    Looking for ideas?

    TIA.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Custom formatted date in ctab query (A2K)

    Use the ORDER BY clause to sort the rows.
    You could write some VBA code to setup the PIVOT clause for the sorting of the columns.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Custom formatted date in ctab query (A2K)

    You would have to use Column Headings property (Query Properties dialog) to specify sort order for the column headings since the Format function results in a text string. In SQL these values will equate to the values listed in the optional IN clause following the PIVOT clause. Example:

    TRANSFORM Nz(Count([OrderID]),0) AS [Orders Count]
    SELECT Orders.CustomerID
    FROM Orders
    GROUP BY Orders.CustomerID
    PIVOT Format([OrderDate],"mm/yyyy") In ("01/1997","02/1997","03/1997","04/1997","05/1997","06/1997")

    This example from NorthWind database. If you don't want to manually enter the fixed column headings or they may often change, you can use VBA to generate the SQL statement dynamically.

    HTH

  4. #4
    New Lounger
    Join Date
    Nov 2001
    Location
    Virginia
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom formatted date in ctab query (A2K)

    Thanks Mark

    I understand the SQL; thanks for the detail.

    I cheated, in the QBE multiplied year by 100 and added month added as row value in ctab so there is something in the query I do not want,
    plus used column headings.

    The SQL is what you posted.

    Thanks a million.

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Custom formatted date in ctab query (A2K)

    A while back I posted some code that showed how to set crosstab column headings dynamically, but that code would not work in this case because the formatted date column headings make things a bit more convoluted. The attached sample database, using revised code, demonstrates how this can be done using VBA to dynamically create a new crosstab query with formatted date column headings sorted in chronological, not alphabetical order. Date range for query based on user input. This example uses the Orders table from NorthWind.mdb for demonstration purposes. To test open frmXTAB form, all code is in form module. (It was too lengthy to post here.) If using this code in your own project, make sure to set reference to "Microsoft DAO 3.6 Object Library."

    HTH
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •