Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    CrossTab Query as a Report Data Source (97)

    We created a cross tab query that provides the expected results. However, when we use the query as the data source in a report it asks for parameter value for tblOther.dtmInvoice. I can't see the forest for the trees now. The following is the SQL from the query. As stated, the query alone works fine, with no prompts. It is only when running the report that uses the query that OFI's occur.

    TRANSFORM Count(tblOther.pk_Invoice) AS [The Value]
    SELECT tblOther.I_strLocation, Count(tblOther.pk_Invoice) AS [Total Of pk_Invoice]
    FROM tblOther
    WHERE (((Year([tblOther]![I_dtmInvoice]))=[Forms]![frm_IReports]![txtYTDYear]))
    GROUP BY tblOther.I_strLocation
    ORDER BY tblOther.I_strLocation
    PIVOT Format([I_dtmInvoice],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

    Any idea?

    TIA,

    Ken

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: CrossTab Query as a Report Data Source (97)

    I'm not sure what is causing your problem, but have you tried including that field in your SELECT statement? One other possibility is some sort of missing reference that causes the prompt when you try to lash it to a report.
    Wendell

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

    Re: CrossTab Query as a Report Data Source (97)

    Try changing the PIVOT line to include the table name prior to I_dtmInvoice, ie. [tblOther].[I_dtmInvoice]

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

    Re: CrossTab Query as a Report Data Source (97)

    If tblOther.dtmInvoice is not a typo in your post, the problem doesn't seem to be related to the query, since there is no mention of dtmInvoice (as opposed to l_dtmInvoice). I would check if there is anything on the report or in its properties that refers to tblOther.dtmInvoice.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: CrossTab Query as a Report Data Source (97)

    Found it! WHEW!
    Yes, that was a typo, but the real issue was in the properties window of the report where "Order by" had the tblOther.dtmInvoice inserted. I removed it and viola' works as expected.

    Thanks again for your help.

    Ken

Posting Permissions

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