Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subform issue (2003 SP2)

    I have two tables with the same exact column heading created with a variable that makes one 606 rows and the other 575 rows.

    From there I create two identical queries, one from each table, [Tbl1] [Tbl1ss]

    Using the queries, I create a main report with a subreport, Main filtered on regions (there are 5) the sub without the filter acting as a subtotal. The first, using [Tbl1] runs perfectly. I cannot see any difference between the two, however, when I run the second I got the following error:

    You can't use a pass-through query or a non-fixed-column crosstabe query as a record source for a subform or subreport.
    Before you bind the subform of subreport to a crosstab query, set the query's ColumnHeadings property.

    I realize the answer to my problem is somehow defined in the error - but I cannot see it - and the first one works without the error. Arrrgh.

    I'm feeling pretty silly here... Any thoughts?
    There is always a way.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform issue (2003 SP2)

    Is the query used for the problem subreport a crosstab query?

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform issue (2003 SP2)

    YES. for the problem report AND for the report that works. Both are crosstabs.

    Therein lies my issue... why would it work for one and not the other?
    There is always a way.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform issue (2003 SP2)

    Without seeing the database, that's impossible to say.

    Will the query always have the same column headings, or can they change over time?

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform issue (2003 SP2)

    that headings will not change
    There is always a way.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform issue (2003 SP2)

    OK, then try specifying the column headings explicitly:
    - Open the query in design view.
    - Click in an empty part of the upper half of the query window.
    - Activate the Properties window.
    - Click in the Column Headings property.
    - Enter the column headings, each one enclosed in double quotes, and separated by commas, for example

    "North", "East", "South", "West"

    - Save the query.

    Specifying the column headings explicitly makes it easier for Access to work with the crosstab query.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform issue (2003 SP2)

    The Column Headings property looks like this after I did as you sugested:
    "Region","'SubRegion","District","Store Type","Building Type","Store Name","Yr","Adds"

    When I try to run it I get:
    This expression is typed incorrectly or is too complex to be evaluated. For example...

    Hmmm.
    There is always a way.

  8. #8
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform issue (2003 SP2)

    Ignore that!
    The column headings are "2006","2007"... and it works.. the rest are Row Headers.

    I'm the idiot on that one.
    There is always a way.

Posting Permissions

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