numeric data type and Boolean values

[rdougherty]

I am using Access to connect to an SQL server using linked tables. The Yes/No values are apparently stored numerically. I was under the impression that we would merely need to determine which is which and translate or use a lookup table. However, I am curious about why I am experiencing the following behavior. In SQL, if you enter Yes as the criteria for any of these flag fields, it finds data. If you use the numeric code Yes apparently corresponds to (looked it up in the UI of our system), it also works, but returns fewer rows. Below are some examples. Please let me know if you are aware of anything that would explain and provide insight into this.

SELECT dbo_hcc_program.cr_ipcdataentry, dbo_hcc_program.cr_ipcreview
FROM dbo_hcc_program
WHERE (((dbo_hcc_program.cr_ipcdataentry)=Yes));

Returns 1181 rows, still displays numeric value in same field. Note, the Yes value is not text “Yes”, but Boolean true/false.


If you run the same query with the numeric value for “Yes” in our system:

SELECT dbo_hcc_program.cr_ipcdataentry, dbo_hcc_program.cr_ipcreview
FROM dbo_hcc_program
WHERE (((dbo_hcc_program.cr_ipcdataentry)=315910001));

Returns 1015 rows


Please let us know if you have any knowledge about this phenomenon. A cursory search on the web is not turning up anything specific. It is as if the value Yes is being interpreted as a number, but the results are not 1:1.


Thanks,

[JoeM]

How about comparing the results of the two queries, doing an unmatched query from one to the other to see which records are in your first but not your second?
Perhaps you will see a common theme that will give you some clues as to why this is happening.

[rdougherty]

thanks. I think I have an answer, but not an explanation Our db admin just explained that when he tests the same sql in his environemtn (not access) he gets the same count as my numeric criteria, and that the boolean Yes throws a data type error. So, I think Access has something "special" it is doing. No one's heard of that before? Access trying to interpret Yes/No as a number? Why is it even allowing me to input a Yes/No On/off True/False type of value into a query field that has a numeric type? When I tab out of that field, shouldn't it warn me like if I put text in a number field?

Weird...

[June7]

In Access query, if you apply Boolean Yes/True to a number type field, any value other than 0 meets the criteria.

If you specify a number as parameter then only that number will be matched.

In other words, 0 = No/False, anything else = Yes/True.

Access provides a Yes/No type field so this ambiguity is eliminated as only -1 and 0 are allowed in Yes/No field.

[rdougherty]

In Access query, if you apply Boolean Yes to a number type field, any value other than 0 meets the criteria.

If you specify a number as parameter then only that number will be matched.

In other words, 0 = False, anything else = True.

Access provides a Yes/No type field so this ambiguity is eliminated as only -1 and 0 are allowed in Yes/No field.

Thank you thank you!!

[June7]

Might find this interesting http://allenbrowne.com/NoYesNo.html