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;
|
