Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Anderson, Indiana, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Criteria problem (2002 SP3)

    I have a query that when I save it will change the formula in the criteria. Example,

    Not Like "PM" Or Not Like "ADMIN" Or Not Like "DT" Or Not Like "Pdtrpt"

    changes to

    Not Like "PM"
    Not Like "ADMIN"
    Not Like "DT"
    Not Like "Pdtrpt"

    This does not produce the same results. I also have a multiple Or in the field before this one and it does not change it.
    I have not seen Access do this before; if any one knows why or has any ideas please let me know.

    Thank You,

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

    Re: Criteria problem (2002 SP3)

    What you describe is normal behavior, and as far as I can see, the result should be the same. In what way is the result different for you?

    By the way, using Like with a string without wildcards * or ? is useless, the condition <code>Not Like "PM"</code>is the same as [codeNot "PM"[/code].

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria problem (2002 SP3)

    Do you have other criteria in your query that isn't applied to each of your "OR" statements in the design grid?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  4. #4
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Anderson, Indiana, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria problem (2002 SP3)

    Hans,

    the top screen shot is how I created the query and the bottom one is what it looks like when I save, close, and re-open it. The results are different. The way I created the query shows in July, the Red team has closed 8 issues and the way access is changing the query results in 44. Eight is correct and the 44 is the combined issues total for the Red team. This is new to me, as I have never seen Access switch a query on me like this.

    Winston

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

    Re: Criteria problem (2002 SP3)

    This is very strange. The other criteria should have been duplicated to the 2nd, 3rd and 4th lines. I have never seen Access make this mistake...

    BTW, why do you use Like without a wildcard?

  6. #6
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Anderson, Indiana, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria problem (2002 SP3)

    I am use to putting everything in the same row for my criteria, I try very hard to keep all of the final reports that the user will use to complete in 30 seconds or less. I have found putting the criteria in one row has increased my performance up to 50 percent in some of the more complex reports I have used in the past. When the query is first ran, before closing it out. I can tell there is about a 3 to 6 second delay in the results, from what I originally created, to the way Access is changing the query.

  7. #7
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria problem (2002 SP3)

    Winston,
    Try copying/pasting the <>#1/1/1900# down so it's there 4 times...
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Criteria problem (2002 SP3)

    You could try the following, although I'm not sure it'll work:
    - Create the query the way you like it.
    - Switch to SQL view.
    - Save the query.
    - When you edit the query afterwards, don't switch to design view, or if you do, don't save it.

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

    Re: Criteria problem (2002 SP3)

    The initial delay when you run the query the first time has nothing to do with multiple criteria lines. The changed query has to be reoptimized, which is what causes the significant delay. I doubt that the SQL engine cares whether you put your criteria on a single ine or not. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

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

    Re: Criteria problem (2002 SP3)

    Keep in mind that you are assigning criteria to a GroupBy line, which will result in a HAVING clause in the SQL, not just a WHERE clause, so when you save the query and then reopen it, the HAVING clause, which is a groupby statement is kept together, while the WHERE expressions will be scattered across multiple lines as appropriate. What is weird about your screen shot is that the date criterion should also have been repeated in multiple lines.
    Charlotte

  11. #11
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria problem (2002 SP3)

    <hr>What is weird about your screen shot is that the date criterion should also have been repeated in multiple lines.<hr>
    <img src=/S/yep.gif border=0 alt=yep width=15 height=15> (<post#=514,196>post 514,196</post: > )....

    If you manually repeat the date criteria, save and reopen, is it still there?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  12. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria problem (2002 SP3)

    While I don't have the answer to why your query changed its structure in the grid, I wonder whether your query is structured correctly in the first place.

    Not Like "PM*" will return every record in your table except the few that look like "PM". Similarly, searching for Not Like "ADMIN" will return all excet the few Admin records. Combining the two criteria with "OR" will return ALL the records in the table. Did you actually mean to use the "AND" operator in your criteria? And I would bet that replacing the ORs with ANDs will cause the funny behaviour to disappear.

    As others have pointed out, using Like without "*" won't work as expected.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  13. #13
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Anderson, Indiana, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria problem (2002 SP3)

    Hans,

    Driving home last night, I was thinking about what you said about using the

  14. #14
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Anderson, Indiana, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria problem (2002 SP3)

    Thanks it is still there

  15. #15
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Anderson, Indiana, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria problem (2002 SP3)

    Thanks, I did mean to use "AND" operator. After switching the query to use "AND" instead of "OR" the query stopped changing the structure.

Posting Permissions

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