Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Crosstab Query with Parameter (2000)

    I have a query that is a totals query (See attached).
    For one of the fields I have in the criteria field - Like "*" & [Sic Description] & "*".
    I want them to be able to put something like "Plastic" and get everything that has "Plastic" in the Sic Description field.
    I want to use this query in another query (see attached) and make it a Crosstab query. Is this possible to do? The way I have constructed my crosstab query isn't working with the parameter. I get an error (see attached). Is there any way to use a parameter with a crosstab? Thanks for your help.

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

    Re: Crosstab Query with Parameter (2000)

    It usually helps to declare the parameter explicitly:

    Open the first query in design view.
    Select Query | Parameters...
    In the Parameter column, enter [Sic Description] exactly the way it is in the criteria line.
    In the Data Type column, select Text.
    Click OK.
    Save the query.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query with Parameter (2000)

    Thanks Hans that got me past the first problem.
    I am using the crosstab query for a report.
    When I run the report the parameter comes up. I enter "Plastic" and then another parameter comes up and I have to enter "Plastic" a second time.
    If I do not enter anything the second time the parameter comes up, I get everything.
    Entering it twice gets me the correct report but is there a way to get it to only ask once?

    Disregard - I don't know what I did but two parameters do not come up now.
    Thank you for your help....

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query with Parameter (2000)

    Hans - I have to eat my words.
    Both queries work fine.
    When I use the crosstab query to make the report, I get errors.
    For some reason when I enter "Plastic" the report does create even though sometimes the parameter comes up twice (It reverted back to that behavior)
    I get the following error when I enter "Auto" for instance
    "The Microsoft Jet Database engine does not recognize " as a valid field name or expression
    Just running the query works fine but when I use it to create a report it goes bananas.

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

    Re: Crosstab Query with Parameter (2000)

    A common problem when using a crosstab query as record source for a report is that the number and names of the fields (columns) may change. A field that was present when you designed the report may not be available when you run it later on.

    If the total number of possible fields is limited, you can specify them in the Column Headings property of the crosstab query (list them, separated by commas).
    Otherwise, you'll have to use VBA code to make the report dynamic. This is far from trivial, but it can be done. See the thread starting at <post#=365323>post 365323</post#>. My first reply contains links to some posts about this subject, and I attached a demo further down.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query with Parameter (2000)

    Thanks Hans
    Putting the headings in did the trick. I only had three headings.

  7. #7
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query with Parameter (2000)

    Hans,

    I am having the same problem with a crosstab query I am building based on a parameter query that asks for a start date, end date and Ops Group. I get a similar error message, The Microsoft Jet Database engine does not recognize '[Start Date]' as a valid name or expression.

    I set the parameters as you instructed, but the original query pops up an additional parameter box with the Between [Start Date] And [End Date] and then pops up the actual start and end date boxes. If I ignore the first box and plug the dates into the 2nd and 3rd boxes the query runs fine.

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

    Re: Crosstab Query with Parameter (2000)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

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

    Re: Crosstab Query with Parameter (2000)

    If you follow the instructions in the post I referred to, you should be able to reduce the size far enough.

  10. #10
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query with Parameter (2000)

    <P ID="edit" class=small>(Edited by charlotte on 28-Jul-05 20:38. )</P>For your own privacy protection, we encourage you to refer to your profile rather than posting your email address.
    Also, please remember <!rule=10>Rule 10<!/rule>


    Hans,

    I can't get it small enough. Is there a way I can email it to you? You can respond to me directly if you would like.

    Thanks

    Thom

  11. #11
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query with Parameter (2000)

    Sorry, I missed the step about compacting the DB. That worked.

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

    Re: Crosstab Query with Parameter (2000)

    You have to declare your parameters as follow:
    Francois

  13. #13
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query with Parameter (2000)

    Thanks, I didn't understand that Start Date and End Date had to be on separate lines. I thought the parameters were supposed to be the same as in the original query. This worked fine.

    Now I need to figure out how to get the crosstab query to drop zeros in the blank fields for each location that doesn't have a listed issue incident in a particular month. How do I go about that?

    Thanks again.

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

    Re: Crosstab Query with Parameter (2000)

    Open the crostab query in design view.
    Select the Total field.
    Open the property window ( View , Properties or the Properties button)
    In the format line, enter :
    #,#,0,0
    The first # determine the format when the number is positive
    The second # determine the format when the number is negative
    The first 0 determine the format if the number is 0 (zero)
    And the second 0 deternine the format when the number is Null. What you asked in your question.
    Francois

  15. #15
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query with Parameter (2000)

    I must be doing something wrong. I open the query, select the Total field by clicking on the top to select that column in the grid and open the Properties dialogue. I try to type the format you recommended in the format line but it won't let me type a comma between the two zeros. I get
    #,#,00.

    What am I doing wrong this time?

    Thanks for your help.

Page 1 of 2 12 LastLast

Posting Permissions

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