Handling Boolean Fields in MS-SQL and MS-Access

If you have migrated from Microsoft Access to SQL Server or MySql then you have probably encountered the differences with boolean values.

In Microsoft Access you can use true or false in queries, for example:

SELECT * FROM tablename WHERE booleanfield=TRUE
SELECT * FROM tablename WHERE booleanfield=FALSE

SQL Server/MySql require a different approach:

SELECT * FROM tablename WHERE booleanfield=1
SELECT * FROM tablename WHERE booleanfield=0

The Microsoft Access query will not work in SQL Server/MySql and the SQL Server/MySql query will not work in Access as it treats true as -1 not 1.

For a cross-platform solution you can use the following:

SELECT * FROM tablename WHERE booleanfield<>0
SELECT * FROM tablename WHERE booleanfield=0