"Not Like" Filter in Update Query Not Working

[jbt007]

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:

SELECT tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]) AS MonthlyNotes, tblMnth.PeriodFinish
FROM tblMnth
GROUP BY tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]), tblMnth.PeriodFinish
HAVING (((Trim([MNote])) Like "*Projection*") AND ((tblMnth.PeriodFinish)=#9/30/2010#));

This SQL Statement Returns 1,132 records:

SELECT tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]) AS MonthlyNotes, tblMnth.PeriodFinish
FROM tblMnth
GROUP BY tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]), tblMnth.PeriodFinish
HAVING (((Trim([MNote])) Is Null) AND ((tblMnth.PeriodFinish)=#9/30/2010#));

This SQL Statement Returns 0 (zero) Records:

SELECT tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]) AS MonthlyNotes, tblMnth.PeriodFinish
FROM tblMnth
GROUP BY tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]), tblMnth.PeriodFinish
HAVING (((Trim([MNote])) Not Like "*Projection*") AND ((tblMnth.PeriodFinish)=#9/30/2010#));

Notice that the only difference is in the "HAVING" statement of the SQL.
Case 1:

(((Trim([MNote])) Like "*Projection*") AND ((tblMnth.PeriodFinish)=#9/30/2010#))

Or Case 2:

(((Trim([MNote])) Is Null) AND ((tblMnth.PeriodFinish)=#9/30/2010#))

Or Case 3:

(((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!

[ADezii]

Try a slight change in Syntax for the HAVING Clause:

HAVING (Trim([MNote]) <> "Projection Activity") AND (tblMnth.PeriodFinish=#9/30/2010#)

[jbt007]

ADezii - I tried that too, still no luck. I have tried:
<> "Projection Activity"
Not Like "*Projectio n*"
Not Like "*Projectio n Activity*"
Not Like "*Activity* "

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

SELECT tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]) AS MonthlyNotes, tblMnth.PeriodFinish 
FROM tblMnth 
WHERE (((Trim([MNote])) <> "Projection Activity") AND ((tblMnth.PeriodFinish)=#9/30/2010#))
GROUP BY tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]), tblMnth.PeriodFinish;

and still no @#*@&! luck! (Excuse the French)

[ADezii]

Try just the single Criteria, and see if any Records are returned:

'HAVING tblMnth.PeriodFinish=#9/30/2010#

[jbt007]

That works fine too - 1,157 Records (Case 1 + Case 2):

SELECT tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, tblMnth.PeriodFinish
FROM tblMnth
GROUP BY tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, tblMnth.PeriodFinish
HAVING (((tblMnth.PeriodFinish)=#9/30/2010#));

Any further thoughts?

[ADezii]

1. Run the following UPDATE Query removing any Leading or Trailing Spaces before Executing the Main Query:
   

   UPDATE tblMnth SET tblMnth.MNote = Trim([MNote]);

2. Now, modify the HAVING Clause in the Main Query to reflect this change:
   

   HAVING (tblMnth.[MNote] <> "Projection Activity") AND (tblMnth.PeriodFinish=#9/30/2010#);

[jbt007]

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...

[ADezii]

Any chance that you can Upload the DB to me?

[jbt007]

I was able to stripp out confidential info... how/where would i send you the db?

[ADezii]

I am going to Send you a Private Message with my Personal E-Mail Address, you can send it there.

[jbt007]

You should have it...

[ADezii]

Unable to Open the File...

[ADezii]

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'
   

   UPDATE tblMnth SET tblMnth.MNote = "N/A"
   WHERE tblMnth.MNote Is Null;

3. The SQL for qry_ZeroRecords is now:
   

   SELECT tblMnth.ActID, tblMnth.Act_Title
   FROM tblMnth
   WHERE tblMnth.PeriodFinish=#9/30/2010# AND tblMnth.MNote<>"Projection Activity"
   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:
   

   SELECT tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]) AS MonthlyNotes, tblMnth.PeriodFinish 
   FROM tblMnth 
   GROUP BY tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]), tblMnth.PeriodFinish 
   HAVING (((Trim([MNote])) Not Like "*Projection*") AND ((tblMnth.PeriodFinish)=#9/30/2010#));

[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 -

SELECT tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]) AS MonthlyNotes, tblMnth.PeriodFinish
FROM tblMnth
GROUP BY tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]), tblMnth.PeriodFinish
HAVING ((((Trim([MNote])) Not Like "*Projection*") OR
Trim([MNote]) Is Null) AND ((tblMnth.PeriodFinish)=#9/30/2010#));

or use the Nz function to convert nulls to an empty string before testing:

SELECT tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]) AS MonthlyNotes, tblMnth.PeriodFinish
FROM tblMnth WHERE (((Nz([MNote])) Not Like "*Projection*"))
GROUP BY tblMnth.Phase, tblMnth.ActID, tblMnth.Act_Title, Trim([MNote]), tblMnth.PeriodFinish
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.

[jbt007]

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.