Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    40

    Unanswered: Error Running Crosstab from Report

    Nobody responded to my original post, so I thought I'd be a pest and resubmit:

    I have a Parameter/Crosstab query that runs perfectly on its own. When I attempt to run a report based on the query, I get the infamous error:
    The Microsift Jet database engine does not recognize "mmm_test_cross" as a valid field name or expression.
    "mmm_test_cross" is the query name.

    The query has an expression that concatenates text from three fields, then converts the string into Date/Time. The param's apply to this field.

    Any advice?

    Thanks.

  2. #2
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    for some reason, if you base a report on a crosstab query, then you have to re-make the report each time you want to run it. Due to the fact that access only imports the current rows/columns when the report is created. So if data is ever added, some of the columns the report accesses may no longer be present, or there may be extra columns. I still have not found a way around this. But if you do, please let me know.

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Saila and beersnobs
    What you can do in a Cross Tab Query
    in the PIVOT part of the Query ADD THE COLOUMS

    PIVOT [Tabename].Feildname In (1,2,3,4,5,6,7,8,9);

    or

    PIVOT [Tabename].Feildname In ("blue","red","On","Off");

    THIS will create all the Column Heading but only full in the Right one



    add the worst caste feilds then the Reports will not Error

    or right click on the Column and Propertes look for Column Heading
    Last edited by myle; 05-05-04 at 17:30. Reason: Spelling Again
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    this only works if the number of columns is fixed, but if you are using dates/times, you can't be sure of the number of columns, so the report has to be rebuilt each time

  5. #5
    Join Date
    Apr 2004
    Posts
    40
    Saila and myle - In my case the only columns I will need are "Jan" through "Dec," so the PIVOT statement works. The report now runs without error. Thanks to both of you for your feedback! Another lesson learned.

Posting Permissions

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