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!
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.
15 7430
Try a slight change in Syntax for the HAVING Clause: - HAVING (Trim([MNote]) <> "Projection Activity") AND (tblMnth.PeriodFinish=#9/30/2010#)
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: - 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)
Try just the single Criteria, and see if any Records are returned: - 'HAVING tblMnth.PeriodFinish=#9/30/2010#
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?
- Run the following UPDATE Query removing any Leading or Trailing Spaces before Executing the Main Query:
- UPDATE tblMnth SET tblMnth.MNote = Trim([MNote]);
- Now, modify the HAVING Clause in the Main Query to reflect this change:
- HAVING (tblMnth.[MNote] <> "Projection Activity") AND (tblMnth.PeriodFinish=#9/30/2010#);
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...
Any chance that you can Upload the DB to me?
I was able to stripp out confidential info... how/where would i send you the db?
I am going to Send you a Private Message with my Personal E-Mail Address, you can send it there.
Unable to Open the File...
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: - 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.
- 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;
- 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;
- The correct Number of Records (1,132) will now be returned.
- 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#));
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.
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.
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).
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Alastair |
last post by:
Hello guys,
I've been building a search facility for an intranet site I'm part of
developing and we've been building a search engine using Index Server.
It mostly works, however there have been...
|
by: Adam Short |
last post by:
I've never needed it before but now I do!
Does anyone know if you are able to perform a NOT Like search using Classic
ASP ADO?
i.e. myData.Filter = "Ref NOT LIKE '*1234*'"
by the way this...
|
by: napi |
last post by:
I think you would agree with me that a C compiler that directly
produces Java Byte Code to be run on any JVM is something that is
missing to software programmers so far. With such a tool one could...
|
by: kandiko |
last post by:
Hi all,
Here is my dilemma.
I have two tables, one called HS DATA and one called
ImportedHighSchools.
Both tables contain an ID code for each record.
|
by: Branco Medeiros |
last post by:
Hi all,
Using SQL Server 2000, SP4.
I have a table of street names (Rua) whose ids (cod_rua) are foreign
keys into a consumer table (Consumidor). It turns out that the "Rua"
table has many...
|
by: KiwiGenie |
last post by:
Hi..I am trying to make a search form. I am fairly new to access and could well be looking at it completely wrong. I have an unbound form with textboxes in the header for entering different search...
|
by: ThePrinceIsRight |
last post by:
I have a problem with using a subquery in MS Access. The purpose of the sub-query is to create a list of people who have had doctor exams in the past 6 months and exclude them from the main query....
|
by: Aegixx |
last post by:
Ok, extremely wierd situation here:
(I'll post the code below, after the explanation)
I've got a Windows application (.NET 3.5) that has a single Form with
a DataGridView embedded. The user...
|
by: AAaron123 |
last post by:
The aspx file includes the following:
<asp:LoginView ID="LoginView1" Runat="server">
<LoggedInTemplate>
<fieldset>
<asp:label id="message1" runat="server" />
|
by: pbenson |
last post by:
I'm a novice at Access and am having trouble with a Not Like query. I'm trying to remove rows with the term "Accrual" from a table. I know there are 16 rows that have the term Accrual, but when I...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| |