0 and -1 in query on yes/no field

[jason]

Question: I am using a yes/no boolean fld in in one of my tables and
attempting to exclude/include certain records in query based on the value.

Thus, if "yes" show only those records and vice versa. Yet the query only
works if I use "0" and "-1" as the checks. Look here:

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;


.....Why is this. If I tried to use "yes"/"no" or "1"/"0" the query will not
work.....don't understand...anyone else does?

Thanks
Jason

 

[Fredg]

Jason,
A check box Yes or No is not a text "Yes" or "No".
The Yes is a number value (-1). No is a number value (0).
Simply do not use the quotes around them.
WHERE TblListings.Active=Yes AND ..... etc.
should work

The same holds true if you use True or False.
WHERE TblListings.Active=True AND ... etc.

 

[John Vinson]

Thus, if "yes" show only those records and vice versa. Yet the query only
works if I use "0" and "-1" as the checks. Look here:

That's correct. Access stores Yes/No fields using 0 as FALSE, -1 as
TRUE.

 

[jason]

Thanks.

Jason,
A check box Yes or No is not a text "Yes" or "No".
The Yes is a number value (-1). No is a number value (0).
Simply do not use the quotes around them.
WHERE TblListings.Active=Yes AND ..... etc.
should work

The same holds true if you use True or False.
WHERE TblListings.Active=True AND ... etc.
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.

Question: I am using a yes/no boolean fld in in one of my tables and
attempting to exclude/include certain records in query based on the value.

Thus, if "yes" show only those records and vice versa. Yet the query only
works if I use "0" and "-1" as the checks. Look here:

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;


....Why is this. If I tried to use "yes"/"no" or "1"/"0" the query will not
work.....don't understand...anyone else does?

Thanks
Jason

 

[jason]

Thanks.

That's correct. Access stores Yes/No fields using 0 as FALSE, -1 as
TRUE.