Results 1 to 5 of 5
  1. #1
    Cecil Rhoades

    Transform (pivot) query w/dynamic criteria

    using Access 97 SR1

    Criteria in a query [Enter a Value] works fine. However in a
    FROM ...
    WHERE MyData BETWEEN '1' AND '10'
    works but
    WHERE MyData BETWEEN [Enter Value 1] and [Enter Value 2] does not.

    Am I missing something or is this Access' behaviour.


  2. #2

    Re: Transform (pivot) query w/dynamic criteria

    My guess the problem is the "TRANSFORM".

  3. #3
    Cecil Rhoades

    Re: Transform (pivot) query w/dynamic criteria

    Your guess is correct. Transform is not in the SQL92 or SQL3 specs, and therefore must be a Microsoft construct. So is the dynamic criteria, [Enter Criteria Here]. Therefore, my question, "Am I stuck with this behaviour in Access or did I miss something?"

    There are workarrounds. I could put my data in a lookup table, or in a form field, or pass it to the Tag property of a report. At least I believe all of these will work.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: Transform (pivot) query w/dynamic criteria

    No, you aren't stuck. Just set the parameters explicitly by opening the Query menu from the query grid and selecting parameters. Put your parameters in there too, not just in the criteria line, and your cross tab will work properly. Your sql should look something like ...

    PARAMETERS [Enter criteria here] Text ( 255 );

  5. #5
    Cecil Rhoades

    Re: Transform (pivot) query w/dynamic criteria

    I did not realize that Access recognized the Parameters keyword. I tried typing it in, but recieved an error. Now I see it is a simple matter of a semicolon at the end of the Parameters line. I have always wanted a computer that did what I wanted, not what I told it.


Posting Permissions

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