Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    1

    Unhappy Unanswered: Building a dynamic cross tab query

    Hello All

    I am working on a cross tab query in Access but I don't know whether it seems possible or not.
    I have a form with combo boxes and according to what I select in the combo boxes I want these to be appearing in my cross tab query.
    This is my code:
    TRANSFORM Last([PRICE]) AS [The Value]
    SELECT [CODE], [FULLDESC]
    FROM QRYPRICE_PERSTORE
    WHERE ((QRYPRICE_PERSTORE.FULLDESC=[Forms]![frmPricingComparison]![Startprod]) AND (QRYPRICE_PERSTORE.STORE_NAME=[Forms]![frmPricingComparison]![STORE1])) OR ((QRYPRICE_PERSTORE.FULLDESC=[Forms]![frmPricingComparison]![Endprod]) AND (QRYPRICE_PERSTORE.STORE_NAME=[Forms]![frmPricingComparison]![STORE2]))
    GROUP BY [CODE], [FULLDESC]
    PIVOT [STORE_NAME];
    The database is giving me an error as in, it can't bind the name [Forms]![frmPricingComparison]![Startprod]) which seems weird because this expression is working in other queries and the database is binding the name.
    So please if anyone can help me, I would be very grateful...
    Thank you...

    Nathalie

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try building a query which filters based upon the entries in your form, and then build your crosstab from the query. This may simplify your crosstab code.

    blindman

  3. #3
    Join Date
    May 2003
    Posts
    26

    Re: Building a dynamic cross tab query

    You need to define the parameters for the form fields first in your crosstab design. While in your crosstab query design select from the menu, Query then Parameters .... or in SQL view you can paste the following code before the TRANSFORM section.

    PARAMETERS [Forms]![frmPricingComparison]![Startprod]) Text (255), [Forms]![frmPricingComparison]![STORE1]
    (Text 255), [Forms]![frmPricingComparison]![Endprod]) text (255), [Forms]![frmPricingComparison]![STORE2] Text (255)


    Hope this helps!


    Originally posted by nathalie
    Hello All

    I am working on a cross tab query in Access but I don't know whether it seems possible or not.
    I have a form with combo boxes and according to what I select in the combo boxes I want these to be appearing in my cross tab query.
    This is my code:
    TRANSFORM Last([PRICE]) AS [The Value]
    SELECT [CODE], [FULLDESC]
    FROM QRYPRICE_PERSTORE
    WHERE ((QRYPRICE_PERSTORE.FULLDESC=[Forms]![frmPricingComparison]![Startprod]) AND (QRYPRICE_PERSTORE.STORE_NAME=[Forms]![frmPricingComparison]![STORE1])) OR ((QRYPRICE_PERSTORE.FULLDESC=[Forms]![frmPricingComparison]![Endprod]) AND (QRYPRICE_PERSTORE.STORE_NAME=[Forms]![frmPricingComparison]![STORE2]))
    GROUP BY [CODE], [FULLDESC]
    PIVOT [STORE_NAME];
    The database is giving me an error as in, it can't bind the name [Forms]![frmPricingComparison]![Startprod]) which seems weird because this expression is working in other queries and the database is binding the name.
    So please if anyone can help me, I would be very grateful...
    Thank you...

    Nathalie

Posting Permissions

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