In Access, a Boolean field or Yes/No field can take the values True (-1) of False (0). To see this you could look at ?clng(false) and ?clng(true) in the immediate window, also in Access if you don’t give a default for a Boolean field, it is treated as FALSE.

SQL Server is a different, True(1) and False(0), and it uses a BIT field for the Boolean value. Also if you don’t supply a value the default is NULL.

There is a little confusion when you link to one of these tables with a default TRUE, and in this article we will explain what is going on.

Example Boolean Tables

Let us start by creating three tables, one with no default for the Boolean, and the other two with a True and False default for the Boolean.

CREATE TABLE BooleanNoDefault ( ID INT PRIMARY KEY, TheBoolean BIT ) GO CREATE TABLE BooleanDefaultTrue ( ID INT PRIMARY KEY, TheBoolean BIT DEFAULT 1 ) GO CREATE TABLE BooleanDefaultFalse ( ID INT PRIMARY KEY, TheBoolean BIT DEFAULT 0 ) GO

Inserting Data

Next we link to the three tables and insert a row in each table and start inserting some data.

Now lets save each row and see what we get.

Now let us switch into the Management Studio in SQL Server and see what we get.

SELECT * FROM BooleanNoDefault Go SELECT * FROM BooleanDefaultFalse Go SELECT * FROM BooleanDefaultTrue Go

This gives the following result:-

So lets now analyse our results.

No Default

This is a very dangerous approach to take, why ? The reason is that whilst Access only allows True and False, SQL Server allows True, False and NULL. So as long as you write code in DAO in Access, the NULL will look like a 0, but if you write code in SQL Server it will not.

Default false

It is more by luck than anything else that we tend to think of a Boolean as being initially FALSE, so most developers’ with MUST will not see an issue here, and because Access shows a 0 value everything is consistent.

Default True

Now we can see a real problem happening. The user sees False when they start entering a record, and then as if by Magic they see the value true! This could potentially represent a serious issue of confusion. Read on to understand what is going on here.

What is happening?

Well the unfortunate fact is that when you start to enter data and the data has default values (defined in SQL Server), the defaults are not set until the records are saved in SQL Server. Unlike in Access tables where you see the defaults as soon as you start typing.

Best Practise

Unless for a specific reason you want a Boolean to contain the value NULL, giving all Booleans a default value is a good idea.

If you want the Boolean to default to True, then you need to have some additional properties set or some code on a form to handle this, if using a DataSheet then unfortunately there is not a work around.

Below we see that by setting a default on a form for the control to match the default in SQL Server, we get a consistent interface.

Now when viewing the form we get a consistent default displayed.

I hope from reading this aricle that you will appreciate that booleans and defauts in SQL Server need a little care.

Top of Page Top of Page

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