Added qry bln condition not behaving correctly

  • Thread starter Thread starter jason
  • Start date Start date
J

jason

I have recently tried to extend my query to incorporate a newly added
boolean fld [yes/no] and only show those records where the value of the
field is ="yes".

The query already has two parameters in play. I do not want to affect the
current working of the query except to add the additional condition of "yes"

But, all attempts have failed thus far as it seems to break my existing
conditions. Here is what it looks like - the WHERE clause is where its at -
is my logic correct or are my AND statemetns contradictory:

PARAMETERS LID Long, ConID Long;
SELECT tblListings.Name AS Name, tblListings.*, tblModel.*, tblYacht_Type.*,
tblCompany.*, tblState.*, tblCountry.*, qry_PriceChanges.*,
tblListings.ListingsID AS ListingsID, tblLocation_Status.*,
tblCondition.Condition AS Condition, tblMarketStatus.Market_Status AS
Market_Status, tblCharterStatus.Charter_Status AS Charter_Status,
tblBroker.Broker AS ListingBroker, tblBroker.Broker_Name AS Broker_Name,
tblBroker.Broker_Email AS Broker_Email, tblCompany.Company AS Company,
tblListings.Active
FROM tblBroker INNER JOIN (tblCharterStatus INNER JOIN (tblMarketStatus
INNER JOIN (tblCondition INNER JOIN ((tblLocation_Status INNER JOIN
(tblYacht_Type INNER JOIN (tblState INNER JOIN (tblModel INNER JOIN
(tblCompany INNER JOIN (tblCountry INNER JOIN tblListings ON
tblCountry.CountryID = tblListings.Country_ID) ON (tblCompany.CompanyID =
tblListings.Company_ID) AND (tblCompany.CompanyID = tblListings.Company_ID))
ON tblModel.ModelID = tblListings.Model_ID) ON tblState.StateID =
tblListings.State_ID) ON tblYacht_Type.YachtTypeID =
tblListings.Yacht_Type_ID) ON tblLocation_Status.Location_StatusID =
tblListings.Location_Status_ID) LEFT JOIN qry_PriceChanges ON
tblListings.ListingsID = qry_PriceChanges.ListingsID) ON
tblCondition.ConditionID = tblListings.Condition_ID) ON
tblMarketStatus.MarketStatusID = tblListings.Status_ID) ON
tblCharterStatus.CharterStatusID = tblListings.Charter_Status_ID) ON
tblBroker.BrokerID = tblListings.ListingBrokerID
WHERE (
((tblListings.Active)=-1)
AND (([LID]) Is Null)
AND ( ([ConID]) Is Null)

)
OR (((tblListings.ListingsID)=[LID]) AND (([ConID]) Is Null))
OR ((([LID]) Is Null) AND ((tblListings.Condition_ID)=[ConID]))
OR (((tblListings.ListingsID)=[LID]) AND
((tblListings.Condition_ID)=[ConID]))
ORDER BY qry_PriceChanges.ChangeDate DESC;

....
 
Ok - I eventually got it right - heres the code:

PARAMETERS LID Long, ConID Long, ActiveID Long;
SELECT [tblListings].[Name] AS Name, tblListings.*, tblModel.*,
tblYacht_Type.*, tblCompany.*, tblState.*, tblCountry.*, qry_PriceChanges.*,
[tblListings].[ListingsID] AS ListingsID, tblLocation_Status.*,
[tblCondition].[Condition] AS Condition, [tblMarketStatus].[Market_Status]
AS Market_Status, [tblCharterStatus].[Charter_Status] AS Charter_Status,
[tblBroker].[Broker] AS ListingBroker, [tblBroker].[Broker_Name] AS
Broker_Name, [tblBroker].[Broker_Email] AS Broker_Email,
[tblCompany].[Company] AS Company, [tblListings].[Active]
FROM tblBroker INNER JOIN (tblCharterStatus INNER JOIN (tblMarketStatus
INNER JOIN (tblCondition INNER JOIN ((tblLocation_Status INNER JOIN
(tblYacht_Type INNER JOIN (tblState INNER JOIN (tblModel INNER JOIN
(tblCompany INNER JOIN (tblCountry INNER JOIN tblListings ON
[tblCountry].[CountryID]=[tblListings].[Country_ID]) ON
([tblCompany].[CompanyID]=[tblListings].[Company_ID]) AND
([tblCompany].[CompanyID]=[tblListings].[Company_ID])) ON
[tblModel].[ModelID]=[tblListings].[Model_ID]) ON
[tblState].[StateID]=[tblListings].[State_ID]) ON
[tblYacht_Type].[YachtTypeID]=[tblListings].[Yacht_Type_ID]) ON
[tblLocation_Status].[Location_StatusID]=[tblListings].[Location_Status_ID])
LEFT JOIN qry_PriceChanges ON
[tblListings].[ListingsID]=[qry_PriceChanges].[ListingsID]) ON
[tblCondition].[ConditionID]=[tblListings].[Condition_ID]) ON
[tblMarketStatus].[MarketStatusID]=[tblListings].[Status_ID]) ON
[tblCharterStatus].[CharterStatusID]=[tblListings].[Charter_Status_ID]) ON
[tblBroker].[BrokerID]=[tblListings].[ListingBrokerID]
WHERE (([LID] Is Null Or [tblListings].[ListingsID]=[LID]) And ([ConID] Is
Null Or [tblListings].[Condition_ID]=[ConID]) And ([ActiveID] Is Null Or
[tblListings].[Active]=[ActiveID]))
ORDER BY [qry_PriceChanges].[ChangeDate] DESC;

jason said:
I have recently tried to extend my query to incorporate a newly added
boolean fld [yes/no] and only show those records where the value of the
field is ="yes".

The query already has two parameters in play. I do not want to affect the
current working of the query except to add the additional condition of "yes"

But, all attempts have failed thus far as it seems to break my existing
conditions. Here is what it looks like - the WHERE clause is where its at -
is my logic correct or are my AND statemetns contradictory:

PARAMETERS LID Long, ConID Long;
SELECT tblListings.Name AS Name, tblListings.*, tblModel.*, tblYacht_Type.*,
tblCompany.*, tblState.*, tblCountry.*, qry_PriceChanges.*,
tblListings.ListingsID AS ListingsID, tblLocation_Status.*,
tblCondition.Condition AS Condition, tblMarketStatus.Market_Status AS
Market_Status, tblCharterStatus.Charter_Status AS Charter_Status,
tblBroker.Broker AS ListingBroker, tblBroker.Broker_Name AS Broker_Name,
tblBroker.Broker_Email AS Broker_Email, tblCompany.Company AS Company,
tblListings.Active
FROM tblBroker INNER JOIN (tblCharterStatus INNER JOIN (tblMarketStatus
INNER JOIN (tblCondition INNER JOIN ((tblLocation_Status INNER JOIN
(tblYacht_Type INNER JOIN (tblState INNER JOIN (tblModel INNER JOIN
(tblCompany INNER JOIN (tblCountry INNER JOIN tblListings ON
tblCountry.CountryID = tblListings.Country_ID) ON (tblCompany.CompanyID =
tblListings.Company_ID) AND (tblCompany.CompanyID = tblListings.Company_ID))
ON tblModel.ModelID = tblListings.Model_ID) ON tblState.StateID =
tblListings.State_ID) ON tblYacht_Type.YachtTypeID =
tblListings.Yacht_Type_ID) ON tblLocation_Status.Location_StatusID =
tblListings.Location_Status_ID) LEFT JOIN qry_PriceChanges ON
tblListings.ListingsID = qry_PriceChanges.ListingsID) ON
tblCondition.ConditionID = tblListings.Condition_ID) ON
tblMarketStatus.MarketStatusID = tblListings.Status_ID) ON
tblCharterStatus.CharterStatusID = tblListings.Charter_Status_ID) ON
tblBroker.BrokerID = tblListings.ListingBrokerID
WHERE (
((tblListings.Active)=-1)
AND (([LID]) Is Null)
AND ( ([ConID]) Is Null)

)
OR (((tblListings.ListingsID)=[LID]) AND (([ConID]) Is Null))
OR ((([LID]) Is Null) AND ((tblListings.Condition_ID)=[ConID]))
OR (((tblListings.ListingsID)=[LID]) AND
((tblListings.Condition_ID)=[ConID]))
ORDER BY qry_PriceChanges.ChangeDate DESC;

...
Click to expand...
 
Back
Top