Special Cases -- True and False / Null and Zero Length String

Writing a report requires you to be aware and cautious about two different values in Access. One is the True/False value and the other is a Null or Zero Length String.

True and False Values

In SQL Server a True Value is stored as a number 1. Access recognizes True as being a number -1 and this sometimes presents a problem when writing reports and queries. There are two simple solutions for this:

1) Use <>0

0 is the value for false in both Access and SQL server. If you use the value not equal to 0 (<>0) you get True. So this will return all your true values.

2) Use True

If you use the value True it will also return the correct values.

Null and Zero Length String

A Null value is when there has never been a value in a field. It means blank. However, sometimes when a value has been put in and removed, you end up with a Zero Length String rather than a Null value. The trouble is you can’t see the difference when just looking at a field. They both look blank. There is a simple solution for this which is to use the phrases:

1) Not Null and Not “”

This excludes both Null values and Zero Length Strings

2) Null or “”

This includes fields that have no entry.