Brian:
It’s the parameter you examine for IS NULL, not the field. If the parameter
is NULL then the parenthesised expression will evaluate to TRUE for every
row. I also suspect the outer Boolean operation should be an AND rather than
an OR. Otherwise if one parameter was entered and the other left NULL every
row would be returned, whereas I'd guess its intended that only the rows
matching the entered parameter be returned.
SELECT repair4b.*, DisciplineLinker.Discipline, AircraftExperience.Sector
FROM (repair4b LEFT JOIN AircraftExperience
ON repair4b.SS_ = AircraftExperience.repair4bID)
LEFT JOIN DisciplineLinker
ON repair4b.SS_ = DisciplineLinker.Repair4bID
WHERE
(AircraftExperience.Sector=[forms]![formQamainMainsearchQuery].[sectorbox]
OR [forms]![formQamainMainsearchQuery].[sectorbox] IS NULL)
AND
(DisciplineLinker.ID=[forms]![formQamainMainsearchQuery].[disciplinebox]
OR [forms]![formQamainMainsearchQuery].[disciplinebox] IS NULL);
The WHERE clause of a query is simply an expression which evaluates either
to TRUE or to FALSE for every row. If its TRUE the row is returned, if its
FALSE it isn't. So with even the most complex expression all you have to
consider is will it be TRUE for the set of criteria you want to apply to each
row to be returned. Look at the expressions from the inside outwards:
In the context of each row in the tables, do EITHER of the of the criteria
in the parenthesised expressions evaluate to TRUE? If so then the whole
parenthesised expression evaluates to TRUE because it’s a Boolean OR
operation. Do BOTH parenthesised expressions evaluate to TRUE? If so then
the whole WHERE clause evaluates to TRUE because it’s a Boolean AND
operation. If one or both parenthesised expressions evaluate to FALSE,
however, then the whole WHERE clause evaluates to FALSE.
Ken Sheridan
Stafford, England
SMA007 said:
Ken,
Thank you, that works. I think I have a general mis-understanding of
queries. I have created a fairly large database and up to this point all SQL
statements and queries and form coding has been fairly simple.
If I add the 3rd table and sql I get this, which I think should just allow
for another set of additional records which does ot affect the first
contition. However now the first condition retruns all records no matter what
I select. and of course the record set has gone read-only. (I can overcome
this by creating a linked form later.)
SELECT repair4b.*, DisciplineLinker.Discipline, AircraftExperience.Sector
FROM (repair4b LEFT JOIN AircraftExperience ON repair4b.SS_ =
AircraftExperience.repair4bID) LEFT JOIN DisciplineLinker ON repair4b.SS_ =
DisciplineLinker.Repair4bID
WHERE
(((AircraftExperience.Sector)=[forms]![formQamainMainsearchQuery].[sectorbox]
Or (AircraftExperience.Sector) Is Null)) OR
(((DisciplineLinker.ID)=[forms]![formQamainMainsearchQuery].[disciplinebox]
Or (DisciplineLinker.ID) Is Null));
This is not correct either...
Thanks again for your help, I am trying for an epiphany here.
Brian
Ken Sheridan said:
If you test for a pattern match against the * wildcard character this will,
as you've found out, exclude any rows from the table on the left side of the
left outer join where there is no matching row on the right side. This is
because the value of the DisciplineLinker.ID column is NULL where there is no
matching row, and any comparative operation involving a NULL always
evaluates to NULL. Even comparing NULL with NULL produces a NULL; think of
NULL as 'unknown' and this makes clearer sense. Consequently the WHERE
clause does not evaluate to TRUE for the missing rows.
The way to do it is to test for the parameter being NULL like so:
SELECT repair4b.*, DisciplineLinker.Discipline
FROM repair4b LEFT JOIN DisciplineLinker
ON repair4b.SS_ = DisciplineLinker.Repair4bID
WHERE DisciplineLinker.ID =
[forms]![formQamainMainsearchQuery].[disciplinebox]
OR [forms]![formQamainMainsearchQuery].[disciplinebox] IS NULL;
If the parameter IS NULL the Where condition will now evaluate to TRUE for
every row, so all will be returned.
Ken Sheridan
Stafford, England
:
I have a main employee table that used to contain about 50 extra fields
pertaining to employees skills and experience. In an effort to normalize
data. I have created 2 extra tables that are linked one to many (enforce
referencial integrity) to the employee record via a foreign key (just a term
I believe, no setting in the table), one table is for skills and the other is
for experience. I have created a query with the employee table (one) on the
left and the other table (many) on the right. ultimately I want both tables
on the right, for testing I have joined only one.
The problem here is that if I leave the parameter box empty, I only get
records from repair4b where where the "DisciplineLinker" table has atleast 1
entry for the employee. I need to have all records from the left table if
disciplinelinker.id is left blank by the parameter.
I am not using the form to pass the parameters at this point.
SELECT repair4b.*, DisciplineLinker.Discipline
FROM repair4b LEFT JOIN DisciplineLinker ON repair4b.SS_ =
DisciplineLinker.Repair4bID
WHERE (((DisciplineLinker.ID) Like
Nz([forms]![formQamainMainsearchQuery].[disciplinebox],"*")));
Thanks for any help and general guidance.
Brian
