In Access a Boolean or Yes/No field has a value of -1 (True) and 0 (False), and Access is clever in that when you do not give a default value for a Boolean, and don’t enter a value it is assumed to be 0 (False).

In the local Access table below we have entered 3 records, and where we have not entered a value this looks identical to where we have unchecked a value. This behaves consistently in the tabular datasheets, queries and all other areas in the product

SQL Server uses a BIT field for a Boolean values and allows a Boolean to be tri-state, 1 (True), 0 (False) and NULL.

In this article we want to look at what happens when you have a view in SQL Server that generates a calculated Boolean field and how that appears to an Access application. But before we get to the really interesting part let us continue to explore what is happening with a basic linked table.

In SQL Server tables it is a very good idea to ensure that Booleans have a default value as 1 or 0, otherwise the NULL values can get you very confused as they are neither True nor False from the perspective of anything written in SQL Server.

Below is a short script for creating a boolean BIT field and testing the results. This script can also be downloaded at the end of this article.

CREATE TABLE BooleanTest( AnId INT IDENTITY(1,1) PRIMARY KEY, ABoolean BIT, ADescription VARCHAR(50) ) GO INSERT INTO BooleanTest(ABoolean,ADescription) VALUES (1,'This is true') GO INSERT INTO BooleanTest(ABoolean,ADescription) VALUES (0,'This is false') GO INSERT INTO BooleanTest(ADescription) VALUES ('This is null') GO -- False Test SELECT * FROM BooleanTest WHERE ABoolean = 0 GO -- True Test SELECT * FROM BooleanTest WHERE ABoolean = 1 GO -- NULL Test SELECT * FROM BooleanTest WHERE ABoolean IS NULL GO

Below we see the table linked from Access.

Below we see how Access translates the 1 for True to -1, and the NULL as 0.

Next let us examine how queries in Access behave on a linked table.

This gives the following result:-

This gives the following result:-

And when we use the third criteria we get the following:-

So in summary, here the Access SQL is behaving exactly the same as SQL Server, but the datasheet is showing 0 rather than NULL.

A user could be forgiven for getting very frustrated with applying a filter to this.

Which gives the following (and the other 0 just vanishes!): -

Upon reflection it appears that Access is doing the very best that it can, although it is a bit inconsistent. To solve all the above problems just ensure that your Booleans always have a default value.

We have the following article Boolean Default Values, when linking Access to SQL Server that explains various issues with using Forms and default values for Boolean fields.

Linked Views and Booleans

Now let us consider having a complex piece of logic in a view, to make things simple here we will use a CASE statement.

CREATE VIEW ProblemBoolean AS SELECT *, CASE WHEN ABoolean = 1 THEN 1 ELSE 0 END AS MyComplexlogic FROM BooleanTest GO

Now we link from Access to this SQL Server View.

This displays the following:-

Now although this is probably not what you expected, Access is again behaving as best it can, because it could not possibly have worked out the data type for the field MyComplex. It is a testimony to Access how it in general can see through a View to understand that a Boolean is indeed a Boolean and correctly translate the value.

So let us now be explicit with the data type of our calculated field MyComplex.

CREATE VIEW ProblemBooleanBetter AS SELECT *, CONVERT(BIT,CASE WHEN ABoolean = 1 THEN 1 ELSE 0 END) AS MyComplexlogic FROM BooleanTest GO

And again re-link the view.

Now we get the following:-

You can see from these examples, that when you are writing SQL in SQL Server which generates Boolean results, it is a very good idea to explicitly cast the data type to a Boolean if you intend to link these views back to Access.

You could also try and avoid this problem by making the view return -1 rather than 1, but we would advise against that because you will then possibly end up with views in SQL Server that could return 1 or -1 as true.

Thanks

I would like to thanks Bryan Bagust of CRC Associates for bringing this very subtle but significant issue to our attention.

Code Download

The sample code described in this article can be downloaded using the following link. (click here)

Top of Page Top of Page

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz