AlphaPress Publishing
P.O. Box 5942 Albany, NY 12205
Tel: 518-250-3890
Email: alphapress@hotmail.com  


  
Click to buy this book from Amazon

Section Menu 

News

Pindaro is now working on a comprehensive book on SQL Server 2014.  For any comments you can email the author at alphapress@hotmail.com.

  

 

 

Compare fields using the CBool() function

To contact the author click here

This article is from the book "Access 2007 Pure SQL

To download the sample database click here


 

The CBool() function

Compare customer first and last name values

Discussion:

The cbool() function will return a true or false value.  To demonstrate its usefulness, let us assume that a coworker comes to our office and says that something is wrong with the mailing labels they are producing.  On certain occasions, the last and first name fields print the same.  Obviously, someone typed in the same value as both first and last name in the database.  We can use the cbool() function to find the records for which this occurs. 

Code:

SELECT CustomerID, cbool([lastname]=[firstname]) As TrueFalse

FROM customers

 

Result:

Whenever you see 0 in Access 2007, this means the result is false, or, in other words, the last and first names have different values since you checked for equality.  If you see -1, the result is true, which means that the first and last names have identical values.  As you scroll through the records, you will notice that you get a -1 for customerid = 19 and an error for 106.

This is cause for investigation.  For record 19, last and first names are the same, while for record 106, the first name value is missing!  Consequently, with one function, we found two errors that would mess up our coworker’s job.  Now, we can correct them and send him on his way to produce correct mailing labels.