PBDR.COM

About   -   Contact   -   Purchase   -   Search   -   What's New

 
 
Handling null fields in queries
A potential problem experienced when retrieving data from tables is dealing with null values. These are sourced from fields in tables, which are designed to accept data or be left blank. Examples of such fields would be post code, fax number, maiden name, where the possibility exists that the field is not applicable or simply unknown. The problem becomes more involved if these fields represent numeric data, in financially orientated databases. If the omission of a value is equivalent to a zero amount, then code must be added to check for null values and substitute these for zero. One method is to iterate thought the recordset & seek out nulls, but a more efficient approach is to avoid this stage altogether and apply a substitution in the query. To achieve this the Immediate If function is employed to populate an alias for each nullable field. The function is used as follows:
   strAgeCategory = IIf(intAge > 18, "Adult", "Minor")
where the integer, intAge is used as a condition to determined the state of the age category string, StrAgeCategory. Similarly, the same method can be used in an query as follows:
   SELECT IIf(IsNull(maiden_name), "N/A", maiden_name) AS IIfMaidenName,
   IIf(IsNull(postcode), "Not Supplied", postcode) AS IIfPostCode
   FROM tblDetails;
 

Top of Page

Legal Notice

Ken Howe 2011