469,349 Members | 1,533 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,349 developers. It's quick & easy.

"Not Like" Filter in Update Query Not Working

jbt007
40
All - This is probably something simple, but for the life of me I can't find the problem. Any help would be greatly apprecieated.

This SQL statment returns 25 records:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]) AS MonthlyNotes, tblMnth.PeriodFinish
  2. FROM tblMnth
  3. GROUP BY tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]), tblMnth.PeriodFinish
  4. HAVING (((Trim([MNote])) Like "*Projection*") AND ((tblMnth.PeriodFinish)=#9/30/2010#));
  5.  
This SQL Statement Returns 1,132 records:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]) AS MonthlyNotes, tblMnth.PeriodFinish
  2. FROM tblMnth
  3. GROUP BY tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]), tblMnth.PeriodFinish
  4. HAVING (((Trim([MNote])) Is Null) AND ((tblMnth.PeriodFinish)=#9/30/2010#));
  5.  
This SQL Statement Returns 0 (zero) Records:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]) AS MonthlyNotes, tblMnth.PeriodFinish
  2. FROM tblMnth
  3. GROUP BY tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]), tblMnth.PeriodFinish
  4. HAVING (((Trim([MNote])) Not Like "*Projection*") AND ((tblMnth.PeriodFinish)=#9/30/2010#));
  5.  
Notice that the only difference is in the "HAVING" statement of the SQL.
Case 1:
Expand|Select|Wrap|Line Numbers
  1. (((Trim([MNote])) Like "*Projection*") AND ((tblMnth.PeriodFinish)=#9/30/2010#))
Or Case 2:
Expand|Select|Wrap|Line Numbers
  1. (((Trim([MNote])) Is Null) AND ((tblMnth.PeriodFinish)=#9/30/2010#))
Or Case 3:
Expand|Select|Wrap|Line Numbers
  1. (((Trim([MNote])) Not Like "*Projection*") AND ((tblMnth.PeriodFinish)=#9/30/2010#))
Case 1 and Case 2 work as expected. Why doesn't Case 3 return the same number of records as Case 2? The only values in MNote (a text field, 255) are Null or "Projection Activity" string.

I must be overlooking something simple...

Thanks!
Dec 13 '10 #1

✓ answered by gershwyn

It is a special property of the Null value that any comparison made on it will also return Null. Any test using Like, =, >, etc. will not include Null values, since the comparison fails. The same is true for Not Like.

To amend your original SQL, you can either explicitly include null values -
Expand|Select|Wrap|Line Numbers
  1. SELECT tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]) AS MonthlyNotes, tblMnth.PeriodFinish
  2. FROM tblMnth
  3. GROUP BY tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]), tblMnth.PeriodFinish
  4. HAVING ((((Trim([MNote])) Not Like "*Projection*") OR
  5. Trim([MNote]) Is Null) AND ((tblMnth.PeriodFinish)=#9/30/2010#));
or use the Nz function to convert nulls to an empty string before testing:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]) AS MonthlyNotes, tblMnth.PeriodFinish
  2. FROM tblMnth WHERE (((Nz([MNote])) Not Like "*Projection*"))
  3. GROUP BY tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]), tblMnth.PeriodFinish
  4. HAVING (((tblMnth.PeriodFinish)=#9/30/2010#));
If you don't need null values for anything (and I don't see what value they would add to a note field, personally) getting them out of your data will save you the headache of dealing with them in other queries as well. Run an update query on your existing data as ADezii suggested. Disallow nulls in that field and set a default value (an empty string would work fine) to prevent them going forward.

15 6995
ADezii
8,800 Expert 8TB
Try a slight change in Syntax for the HAVING Clause:
Expand|Select|Wrap|Line Numbers
  1. HAVING (Trim([MNote]) <> "Projection Activity") AND (tblMnth.PeriodFinish=#9/30/2010#)
Dec 13 '10 #2
jbt007
40
ADezii - I tried that too, still no luck. I have tried:
<> "Projection Activity"
Not Like "*Projection*"
Not Like "*Projection Activity*"
Not Like "*Activity*"

I also tried changing the "HAVING" to "WHERE" as in:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]) AS MonthlyNotes, tblMnth.PeriodFinish 
  2. FROM tblMnth 
  3. WHERE (((Trim([MNote])) <> "Projection Activity") AND ((tblMnth.PeriodFinish)=#9/30/2010#))
  4. GROUP BY tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]), tblMnth.PeriodFinish;
and still no @#*@&! luck! (Excuse the French)
Dec 13 '10 #3
ADezii
8,800 Expert 8TB
Try just the single Criteria, and see if any Records are returned:
Expand|Select|Wrap|Line Numbers
  1. 'HAVING tblMnth.PeriodFinish=#9/30/2010#
Dec 13 '10 #4
jbt007
40
That works fine too - 1,157 Records (Case 1 + Case 2):

Expand|Select|Wrap|Line Numbers
  1. SELECT tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, tblMnth.PeriodFinish
  2. FROM tblMnth
  3. GROUP BY tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, tblMnth.PeriodFinish
  4. HAVING (((tblMnth.PeriodFinish)=#9/30/2010#));
  5.  
Any further thoughts?
Dec 13 '10 #5
ADezii
8,800 Expert 8TB
  1. Run the following UPDATE Query removing any Leading or Trailing Spaces before Executing the Main Query:
    Expand|Select|Wrap|Line Numbers
    1. UPDATE tblMnth SET tblMnth.MNote = Trim([MNote]);
  2. Now, modify the HAVING Clause in the Main Query to reflect this change:
    Expand|Select|Wrap|Line Numbers
    1. HAVING (tblMnth.[MNote] <> "Projection Activity") AND (tblMnth.PeriodFinish=#9/30/2010#);
Dec 13 '10 #6
jbt007
40
Still same problem... I have also compacted & rebuilt the db, just to be sure. ADezii - thanks for the help but I don't have time to mess around with this any longer so I am going to take a different approach that (I hope) will solve the problem. This is just not making any logical sence. So unless you have an eurika moment thanks for the help and we'll give this one up...
Dec 14 '10 #7
ADezii
8,800 Expert 8TB
Any chance that you can Upload the DB to me?
Dec 14 '10 #8
jbt007
40
I was able to stripp out confidential info... how/where would i send you the db?
Dec 14 '10 #9
ADezii
8,800 Expert 8TB
I am going to Send you a Private Message with my Personal E-Mail Address, you can send it there.
Dec 14 '10 #10
jbt007
40
You should have it...
Dec 14 '10 #11
ADezii
8,800 Expert 8TB
Unable to Open the File...
Dec 14 '10 #12
ADezii
8,800 Expert 8TB
Finally got it. I have arrived at an answer, but it is actually a Band Aid on this bizarre behavior. Here is what I did:
  1. Set the Allow Zero Length Property of the [MNote] Field in tblMnth to No. I'm not really sure if this is relevant, but I'll leave that up to you.
  2. Ran an Update Query setting all NULL Values in the [MNote] Field to 'N/A'
    Expand|Select|Wrap|Line Numbers
    1. UPDATE tblMnth SET tblMnth.MNote = "N/A"
    2. WHERE tblMnth.MNote Is Null;
  3. The SQL for qry_ZeroRecords is now:
    Expand|Select|Wrap|Line Numbers
    1. SELECT tblMnth.ActID, tblMnth.Act_Title
    2. FROM tblMnth
    3. WHERE tblMnth.PeriodFinish=#9/30/2010# AND tblMnth.MNote<>"Projection Activity"
    4. GROUP BY tblMnth.ActID, tblMnth.Act_Title;
  4. The correct Number of Records (1,132) will now be returned.
  5. This SQL Version will now work also:
    Expand|Select|Wrap|Line Numbers
    1. SELECT tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]) AS MonthlyNotes, tblMnth.PeriodFinish 
    2. FROM tblMnth 
    3. GROUP BY tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]), tblMnth.PeriodFinish 
    4. HAVING (((Trim([MNote])) Not Like "*Projection*") AND ((tblMnth.PeriodFinish)=#9/30/2010#)); 
Dec 14 '10 #13
gershwyn
122 100+
It is a special property of the Null value that any comparison made on it will also return Null. Any test using Like, =, >, etc. will not include Null values, since the comparison fails. The same is true for Not Like.

To amend your original SQL, you can either explicitly include null values -
Expand|Select|Wrap|Line Numbers
  1. SELECT tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]) AS MonthlyNotes, tblMnth.PeriodFinish
  2. FROM tblMnth
  3. GROUP BY tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]), tblMnth.PeriodFinish
  4. HAVING ((((Trim([MNote])) Not Like "*Projection*") OR
  5. Trim([MNote]) Is Null) AND ((tblMnth.PeriodFinish)=#9/30/2010#));
or use the Nz function to convert nulls to an empty string before testing:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]) AS MonthlyNotes, tblMnth.PeriodFinish
  2. FROM tblMnth WHERE (((Nz([MNote])) Not Like "*Projection*"))
  3. GROUP BY tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]), tblMnth.PeriodFinish
  4. HAVING (((tblMnth.PeriodFinish)=#9/30/2010#));
If you don't need null values for anything (and I don't see what value they would add to a note field, personally) getting them out of your data will save you the headache of dealing with them in other queries as well. Run an update query on your existing data as ADezii suggested. Disallow nulls in that field and set a default value (an empty string would work fine) to prevent them going forward.
Dec 15 '10 #14
jbt007
40
ADezii - Thanks for all your efforts and the work-around solution. I too found a work-around, however I was really interested in the unlogical behavior of the three queries. Gershwyn really addressed this issue in explaining why the queries functioned the way they did.
Dec 15 '10 #15
ADezii
8,800 Expert 8TB
You are surely correct in stating that gershwyn addressed this issue in excellent fashion, and truly shed light on the Topic. Sometimes there is absolutely nothing like a third opinion! (LOL).
Dec 15 '10 #16

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

235 posts views Thread by napi | last post: by
3 posts views Thread by Branco Medeiros | last post: by
5 posts views Thread by AAaron123 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.