=====================================================================
     Comparing Null Values in R:BASE Reports
     =====================================================================
     PRODUCT: R:BASE            VERSION: 3.X, 4.0,4.5
     =====================================================================
     AREA: REPORTS              CATALOG: FORMS, REPORTS & LABELS
     =====================================================================
 
     There are situations in which you want to check if a column contains a 
     value or not while printing a report. A column that does not contain a 
     value is NULL. Null is a term that means "absence of value". Some 
     common situations where you need to check for nulls are:
 
        *       Conditional printing of an address line.
        *       Counting rows based on the existence of data in a column
        *       Printing below one heading or another
        *       Printing one message or another
        *       Computing with a different factor or percentage
 
     For example, an employee report may have a field to show a "yes"/"no" 
     response based on a birthdate column. The column is a DATE datatype 
     and the report is to print the text "yes" if the employee has their 
     birthdate on file, or "no" if they do not. This type of "conditional 
     printing" needs to test if the column contains a value or not (if the 
     column is null).
 
     There is an easy way to check for nulls in a report column or variable 
     using the IFEQ logical function with report variables. The function 
     IFEQ ("if equals") has four arguments. The function compares the first 
     two arguments and returns the third argument if true, the fourth 
     argument if false. It's like doing an IF...THEN...ELSE. Reports don't 
     let you do IF statements, but defining an expression using the IFEQ 
     function gives you the same capability.
 
     You may not need to check for nulls. On any located field you can 
     define a picture format. The picture format includes a NULL format 
     which allows you to specify a value to print when the column or 
     variable is null (has no data). Use this feature to print comments 
     like "Not Provided" or "None" or "N/A" in the location when the field 
     is null. If no null format is specified, the report prints a blank 
     when the field is null. The null format can be used when you want to 
     print a message or value when the field is null, but the actual data 
     value prints in that location when the field is not null.
 
     You check for a column or variable is null by defining two variables 
     in a report. For example:
 
        vnulldate DATE = ''
        v_on_file = IFEQ(.vnulldate,Birthdate,'no','yes'))
 
     The expression; vnulldate DATE = '' sets up a null date datatype 
     variable for comparisons. Notice that the variable's definition states 
     the variable name followed directly by its datatype, then the equals 
     sign, and finally two quotes (single or double depending on the 
     current quotes setting for your database) with no space between them. 
     It is important to define the variable to two quotes with no space, 
     this sets the variable to null. If you include a space between the 
     quotes you are setting the variable to blank, not null. This type of 
     expression can be used with any datatype to create a null variable.
 
     The logical function IFEQ returns the true condition to the variable 
     if both values it is comparing are null, it returns the false 
     condition is only one of the values or neither value is null. In the 
     above example, the variable vnulldate is always null. The IFEQ 
     function sets the variable v_on_file to the word "no" if vnulldate and 
     the column Birthdate are both null. If Birthdate has a value, the 
     variable v_on_file is set to "yes". 
 
     The IFEQ expression works properly because you have defined a null 
     variable to compare to the column or variable to be checked. You must 
     compare to a null variable or column, you can't just compare to the 
     null symbol.
               
     One thing to watch out for when checking for null values in numeric 
     columns is zero (0) values in the data. If the datatype of the column 
     being checked is INTEGER, REAL, DOUBLE, NUMERIC or CURRENCY and the 
     ZERO setting in your database is ON, the value 0 will evaluate the 
     same as null. You can avoid this by changing the ZERO setting to OFF. 
     Before setting ZERO OFF, however, check to see if there are other 
     report expressions that require it to be ON. There are situations 
     where you can take advantage of nulls and zeros evaluating the same; 
     see the article "Printing Zero Values as Blanks on Financial 
     Statements" in this Exchange.