Results 1 to 10 of 10
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Bumped into a weird one today.
    Anyone met this before

    Running on Access 2000

    A query has one parameter in it in the supplier criteria row
    It is the only criteria applied to the whole query.

    Parameter was defined as...............

    Like [Enter Supplier Name or Part of Name or Blank for ALL] & "*"

    The parameter is only in the query in one location.
    It is NOT part of a calculation
    It is NOT defined in the Parameters list (maybe that is part of the cause)

    The issue occurred when running the query on its own.

    The parameter question was requested twice although only referenced once.

    If the query was edited and saved the problem went away.
    But later for no reason it then appeared to return.

    The only thing I can think of is that the database had not had a compact and repair for some while.
    I have now made them do this and will see if the issue still arises.

    Just wondering if anyone else had met this.
    Andrew

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    It often helps to declare the parameter explicitly in Query | Parameters... or equivalently in the PARAMETERS section at the beginning of the SQL statement.

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='HansV' post='797986' date='14-Oct-2009 21:15']It often helps to declare the parameter explicitly in Query | Parameters... or equivalently in the PARAMETERS section at the beginning of the SQL statement.[/quote]

    Yes, it may well have been to do with that.
    They were not aware of the parameters declaration section.

    However, I have never encountered this before even when parameters are not formally defined.

    At first I thought it may was a double use of the same parameter with slight spelling change
    First place I checked was the parameters box to see if anything was there but it wasn't.

    It does look like the compact and repair has fixed the problem.
    If it does come back I'll report back.
    Andrew

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I just found this (not sure IF it was cause because it says applies to Access XP (2002)

    But

    MS Article http://support.microsoft.com/kb/287432

    Reports the following

    SYMPTOMS
    When you run a parameterized query, you receive two prompts for each parameter that is defined in the query.

    CAUSE
    Sorting has been applied to the query in Datasheet view rather than in Design view.

    SOLUTION
    I query Design, remove the Order By entry in the Queries Property Window

    Maybe. Never looked there, I'll get them to check
    Andrew

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    YEP.

    I just tested thsi out by sorting in data mode and then re running the query.
    The double prompt issue returns.

    It goes away if you edit the design of the query because that automatically resets the Order By property of the query.

    BUT as soon as anyone sorts the query in datasheet view and then saves it (only if they save it after the sort),
    the problem comes back.

    MS Report this as effecting Access 2000 and 2002.

    Last revised bulletin for 2000
    Article ID: 245467 - Last Review: November 25, 2002


    I just checked and it also does it in Access 2003 (with all SP's up to date)
    And 2007 with all SP's up to date.

    So they have NOT fixed it as an issue in they new about as early as Nov 2002.

    In the database I have now made sure that they use Forms in Databsheet view based on the query rather than displaying the actual query.
    This removes the possibility of users both changing the query and also saving the sort order in the query).

    But it is still an interesting one.

    Andrew

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Thanks for posting the cause and solution!

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='AndrewKKWalker' post='798005' date='15-Oct-2009 08:09']YEP.

    I just tested thsi out by sorting in data mode and then re running the query.
    The double prompt issue returns.

    It goes away if you edit the design of the query because that automatically resets the Order By property of the query.

    BUT as soon as anyone sorts the query in datasheet view and then saves it (only if they save it after the sort),
    the problem comes back.

    MS Report this as effecting Access 2000 and 2002.

    Last revised bulletin for 2000
    Article ID: 245467 - Last Review: November 25, 2002


    I just checked and it also does it in Access 2003 (with all SP's up to date)
    And 2007 with all SP's up to date.

    So they have NOT fixed it as an issue in they new about as early as Nov 2002.

    In the database I have now made sure that they use Forms in Databsheet view based on the query rather than displaying the actual query.
    This removes the possibility of users both changing the query and also saving the sort order in the query).

    But it is still an interesting one.

    [/quote]
    This happens when you set a filter and save the query, so it can be the sort or filter. I hope i am not repeating others here.

  8. #8
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='patt' post='798046' date='15-Oct-2009 02:01']This happens when you set a filter and save the query, so it can be the sort or filter. I hope i am not repeating others here.[/quote]

    GOOD POINT!

    They should FIX it though
    Andrew

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

    They should FIX it though
    Don't hold your breath. The 2002 KB article 287432 indicates that it is a "nofix" issue, and it happens because of the way Jet 4 processes queries, and what is perceived to be a performance issue - they want the compiled version of the query to be sent to Jet, rather than checking to see if there is an ORDER BY or a FILTER setting. For what it's worth, here is the description of the process:

    If you sort the query in Design view, Microsoft Access adds the ORDER BY clause to the SQL statement of the query. If you sort the query in Datasheet view, Microsoft Access stores the sort order in the OrderBy property of the query. When the OrderBy property has been set, either on the property sheet in Design view or in Datasheet view, Microsoft Access will prompt you twice for each parameter in the query. This occurs because Microsoft Access runs the query initially to retrieve the correct records, and then runs it again to apply the sort order that you specified.
    In general, you want to stay away from having users run Parameter queries from the database window, especially if they are updateable. Instead, display the data on a form where they can do filtering and sorting to their hearts content, and make sure they can't save the design of the form. Having those capabilities available to a developer is very important, but don't give them to the end user.
    Wendell

  10. #10
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='WendellB' post='798152' date='15-Oct-2009 16:22']In general, you want to stay away from having users run Parameter queries from the database window, especially if they are updateable. Instead, display the data on a form where they can do filtering and sorting to their hearts content, and make sure they can't save the design of the form. Having those capabilities available to a developer is very important, but don't give them to the end user.[/quote]

    Yep.
    That's what I told them to do.
    Andrew

Posting Permissions

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