Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2003
    Location
    UK
    Posts
    85

    Red face Unanswered: Crosstab query-monthwise dates

    Dear All

    I have a crosstab query. In it there is one field for Analysis date that has been set to group by months. The query is linked to a report.

    Analysis date ranges over 2-3 years. But when I get data of report, it presents the dates as Jan, Feb, Mar etc. and presents all data say for January of all three years under Jan. I want the data to be presented as Jan-01 (for year 2001), Jan-02 (for year 2002) and so on. I have tried to change the date format on crosstab query design grid, but did not work.

    Please help me.

  2. #2
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80

    Re: Crosstab query-monthwise dates

    Instead on using month(date_fld) as your field, try using:
    FORMAT(date_fld,"mmm-yy")


    Good Look!

    IONUT

  3. #3
    Join Date
    Mar 2003
    Location
    UK
    Posts
    85

    Red face

    Thank you very much for your reply.

    I have actually tried it before. When I change the format to "mmm-yy"in crosstab query and run it, nothing appears under months, it doesn't display any data.

    Any other suggestion? Looking forward to your reply.

  4. #4
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80
    I'm not sure where did you put it in your query.

    Your query designer should look like this:



    __________________________________________________ ______
    Field: [ClientName] Month: Format([DateField],"mmm-yy") [Val]
    -------------------------------------------------------------------------------
    Table: [...] [...]
    -------------------------------------------------------------------------------
    Total: Group By Group By Sum
    -------------------------------------------------------------------------------
    Crosstab: Row Heading Column Heading Value
    -------------------------------------------------------------------------------
    Sort:
    -------------------------------------------------------------------------------
    Criteria: NO CRITERIA DEFINED
    -------------------------------------------------------------------------------




    If above doesn't work, I am speachless because I've tried my self on a small set of data and it had worked fine.


    IONUT

  5. #5
    Join Date
    Mar 2003
    Location
    UK
    Posts
    85
    ionut calin

    Thank you for your reply. I was away for some days that is why it took that long to respond.

    You were right, it should have worked like that. When I was changing the date format in already created crosstab query design grid where the date was set as format by month (mmm), nothing was happening but when I created new crosstab query and instead of selecting 'month' for date during wizared, I selected 'date' and later in crosstab design grid I changed format to 'mmm-yy' and it worked. Don't know why but it did the trick.

    Thanks again.

  6. #6
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80
    That's just one more bug from Microsoft.

    The same problem I experienced in MS SQL Server 2k, SP2. When I changed the lenght of a field in one table, the view that was made on that table just refused to work anymore. I had to delete the view and re-create it.

    For SQlServer I have an answer: it writes informations for fields when the view is first created in a systemtable, and than "forgets" to update those information when I change the field in its original table. It might be possible that Access is working in the same way.

    IONUT

Posting Permissions

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