Results 1 to 7 of 7
  1. #1
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Problematic Crosstab Query (2000 SR1)

    We are trying to use a crosstab query to drive a report, and we are trying to use a control on a form to supply a date as a part of the criteria. It fails saying that the form reference is an invalid object. The SQL looks like this

    <font color=448800>TRANSFORM Sum(qry_WeeklyStatistics.EventCount) AS SumOfEventCount
    SELECT qry_WeeklyStatistics.UserName
    FROM qry_WeeklyStatistics
    WHERE (((qry_WeeklyStatistics.Event_Date) Between DateAdd("d",-6,[Forms]![frm_MyForm]![txt_WeekEnd_dt]) And [Forms]![frm_MyForm]![txt_WeekEnd_dt]))
    GROUP BY qry_WeeklyStatistics.UserName
    PIVOT qry_WeeklyStatistics.event_Desc;</font color=448800>

    If we simply replace the <font color=448800>Forms![frm_MyForm]![txt_WeekEnd_dt]</font color=448800> with a valid date, it works fine. By the same token, if we put the expression into the source query (qry_WeeklyStatistics) as a criteria, that query will run just fine, but the crosstab query still fails. Can someone shed some light on what's going on here? Thanks in advance.
    Wendell

  2. #2
    Star Lounger
    Join Date
    Aug 2001
    Location
    Seattle, Washington, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problematic Crosstab Query (2000 SR1)

    Wendell,

    When I've tried to run a similar report (based on a crosstab query, which was based on another query), I had to use the Query . . . Parameters dialog box to get the query to recognize the Text box in the selection form. Used the [Forms]![frmFormName]![txtTextBoxName] name in the Parameter column, and Date/Time in the Data Type column.

    I used the Query . . . Parameters box on the first query, not on the crosstab query itself.

    HTH,

    Tom

  3. #3
    Lounger
    Join Date
    Nov 2001
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problematic Crosstab Query (2000 SR1)

    Define the Parameters in the crosstab as Date/Time

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problematic Crosstab Query (2000 SR1)

    Maybe not elegant but it works.
    Create a function:
    <pre>Function Mydate() As Date
    Mydate = [Forms]![frm_MyForm]![txt_WeekEnd_dt]
    End Function</pre>

    Change the query to
    TRANSFORM Sum(qry_WeeklyStatistics.EventCount) AS SumOfEventCount
    SELECT qry_WeeklyStatistics.UserName
    FROM qry_WeeklyStatistics
    WHERE (((qry_WeeklyStatistics.Event_Date) Between DateAdd("d",-6,Mydate()) And Mydate()))
    GROUP BY qry_WeeklyStatistics.UserName
    PIVOT qry_WeeklyStatistics.event_Desc;
    Francois

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Problematic Crosstab Query (2000 SR1)

    Put the full form reference for txt_WeekEnd_dt into the parameters of the crosstab query. I'm running reports based on a similar situation right now, and it even works in Access 97! The only catch to this is that if you want to open a recordset in code based on the query so that you can tell if there are records for the report, you'll have to use a querydef object and pass the parameters in as values, because opened in code, it won't be able to find the form values for itself.
    Charlotte

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

    Re: Problematic Crosstab Query (2000 SR1)

    Thanks - that worked like a charm - guess I'm running a little low on sleep and sense here. I thought about trying to put a parameter in, but decided it wouldn't work for some strange reason. Thanks for similar responses from RichUK, Francois and Charlotte.
    Wendell

  7. #7
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problematic Crosstab Query (2000 SR1)

    For reference it is in this KB Article:
    Q209778
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

Posting Permissions

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