Yes/No False vs. Yes/No <> True

[supmktg]

Can someone explain why these 2 queries have different results?

1) returns 314 records
SELECT locations.LOCATION_NUMBER, locations.LOCATION_DESCRIPTION,
FROM locations
WHERE (((locations.LOCATION_IS_INACTIVE)<>True))
ORDER BY locations.LOCATION_DESCRIPTION;

2) returns 308 records
SELECT locations.LOCATION_NUMBER, locations.LOCATION_DESCRIPTION,
FROM locations
WHERE (((locations.LOCATION_IS_INACTIVE)=False))
ORDER BY locations.LOCATION_DESCRIPTION;

The table locations is an ODBC linked table from another program. In viewing the records in the table I see 314 records where the field LOCATION_IS_INACTIVE is 0 and 2 where it is -1.

I am going to use <>True, but I would like to understand why this is happening for future reference.

Thanks,
Sup

 

[SOS]

True is equal to -1 and if you have anything else stored besides 0 then <>True would include records with 0 and anything else stored.

 

[supmktg]

That's the issue, there are a total of 316 records. 314 show 0 (False) and 2 show -1 (True) in the Yes/No field. There are no other records. The field defaults to false unless changed to true. There are no null values in that field to screw thing up.

How could = False not return all 314 true records?

 

[SOS]

I can't explain your data without actually seeing it. But we had a situation where a specific grouping was causing us to get a -2 instead of -1 and so I know it is possible to get the wrong number based on the way you set criteria.

 

[supmktg]

I just created a table from the linked ODBC table using a make table query. As expected, 314 records show 0 and 2 show -1 in the yes/no field. When I run my query on this new table, =False now returns the expected 314 records.

There must be something goofy going on in the linked table that I can't see. I have no control over the data, it is written to by a different program. The ODBC driver is read-only, so I have no way of correcting any errors if they exist.

Meanwhile, I will have to use <> True to get around the problem.

Thanks,
Sup

 

[SOS]

Yeah, fun isn't it The things we go through to work around those darn constraints.

Good luck with everything.

 

[Banana]

If you want to experiment, try the query with this:

WHERE InActive = 1

And see what happens.

This may or may not explain the strange behavior.

 

[gemma-the-husky]

try

= " & false

rather

= false

to see if its something to do with the why the compiler is dealing with the text value of false

 

[supmktg]

1 and -1 return the same 2 records. False & 0 return the same 308 records.
= " & false gives me a syntax error. The only thing that works is <>True. I have to assume that the linked table must be holding a few records with the number 0 instead of 0 as False.

Thanks for trying to help,
Sup

 

[Banana]

Fascinating.

What about those test:

= "0"
= 0

?

Also, what is the backend & the ODBC driver you are using?

 

[gemma-the-husky]

I have to assume that the linked table must be holding a few records with the number 0 instead of 0 as False.

a curious comment - i dont thnik there is any difference - but look at the table in design view, and see what the field is defined as - i think sql tables still show -1 and 0 values for yesno datatypes, rather than the chkboxes you see in jet tables

also try

WHERE (((not (locations.LOCATION_IS_INACTIVE) = True) ))

just to see what happens

-------
although you say there are no nulls, it really is the only explanation for this, i feel

try specifically

where inactive is null, to see if you do have some nulls in there.

 

[SOS]

The other question is were you looking at the data directly in the other data source (for example, using SQL Management Studio if working with SQL Server) and not looking at the table in linked view in Access? Because Access might have coerced some stuff which doesn't really exist in the source.

 

[Atomic Shrimp]

Have you identified which records are different between the two queries? Try running a find unmatched query on the two tables - if you can pull out the two rows that are affected, the cause might become clearer on examination of the contents.

 

[neileg]

The other question is were you looking at the data directly in the other data source (for example, using SQL Management Studio if working with SQL Server) and not looking at the table in linked view in Access? Because Access might have coerced some stuff which doesn't really exist in the source.

I suspect this is the case. Access probably doesn't like null values and is displaying the nulls as false. However when you run the query, it interprets the nulls as nulls. Try querying the data for null values. I suspect you'll find your rogue records.

 

[supmktg]

Mystery solved! An unmatched query does indeed show 6 records where the Inactive field is Null.

Meanwhile, in my regular query, Is Null returns 0 records and Not Is Null returns all 316 records.

Thanks to everyone for helping me solve this,
Sup

 

[Banana]

You may already done so, but you will definitely want to make sure that the BIT field in your backend is set to not permit any nulls from now on. This also will avoid any other problems and not just this one query.