Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Filtering Crosstab Query (2002)

    Hi Again,

    I'm working with a crosstab query that has a field of [year] as the column heading. I want the user to be able to type in a year on an unbound box on a form in order to limit the query (and soon to be report) to the year that is specified. I've done this with select queries but keep getting a jet engine error message when I try it with a crosstab query. I even tried putting [Please enter year] into the criteria section of the query but get the same error. The user needs to be able to specify the year as over time there will be many years in the table.

    Thanks,
    Leesha

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

    Re: Filtering Crosstab Query (2002)

    Have you defined the [Please enter year] as a PARAMETER.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filtering Crosstab Query (2002)

    The error I'm getting is "The microsoft jet database does not recognize " as a field name or expression." Interestngly, ever time I go to run the report, after I get the error, if I go into the query, a new column has been added for Year that is identical to one I placed as a row in the crosstab query, except this one does not include include the row setting, but rather a "where". This only happens if I run the report. If I just run the query itself (with the form open and the year in it) it doesn't happen. Either way the filter doesn't happen. I really do need to be able to filter by year and eventually by other areas.

    I've enclosed a stripped down version of the database. The last column in the query is one the database keeps putting there, not me.

    BTW, I actually did my own union query based on the stuff you showed me this summer!!

    Thanks,
    Leesha

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

    Re: Filtering Crosstab Query (2002)

    When I open the form and enter 2004, then run the crosstab query, no records are returned. That suggests something wrong with your criteria. Crosstabs are a little unreliable when you try to use a form control as the criteria. Instead, the simple way is to let the *report* do the filtering by passing IT a where condition, like this.

    <pre>Private Sub cmdOpenGLReport_Click()
    <font color=448800>'Opens the GL Report for the year requested</font color=448800>

    DoCmd.OpenReport "rptGLDollars", acViewPreview, , "[Year]=" & Me.txtYear

    End Sub</pre>


    Just remove the criteria and parameter from the crosstab query and do it this way. I've attached your revised sample, so you can see that it works.
    Charlotte

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filtering Crosstab Query (2002)

    Hi Charlotte!

    Thanks so much for sending the sample for me to understand. Ever since learning to use form controls as criteria I've depended on those with queries as I'm forever forgetting something when I try write it in code. It's good to know in this instance to do it this way (due to crosstab query) as I'll be using a lot of them in this database!

    Thanks again for you help,
    Leesha

Posting Permissions

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