[AccessD] how to use IIF for six different fields

Kostas,

    You wish to merge the contents of six fields into a comma separated string in such a manner that fields with Null value get completely ignored. Judicious combination of + and & operators can provide a convenient alternative in lieu of cumbersome IIf() construct.

    Sample query as given below, should get you the desired results via calculated field named MergedValue. F1 to F6 are the six fields in table T_Data.

    The expression works even if all the six fields are blank. This is because Mid() function, acting upon zero length string, returns a zero length string without generating error, irrespective of the value of numerical argument.

Best wishes,
A.D. Tejpal
------------

Sample Query
====================================
SELECT T_Data.*, Mid((","+[F1]) & (","+[F2]) & (","+[F3]) & (","+[F4]) & (","+[F5]) & (","+[F6]),2) AS MergedValue 
FROM T_Data;
====================================

  ----- Original Message ----- 
  From: Kostas Konstantinidis 
  To: Access Developers discussion and problem solving 
  Sent: Monday, June 08, 2009 02:04
  Subject: [AccessD] how to use IIF for six different fields


  Hi group,
  I have 6 different fields some of them with null values
  How is it possible to use the IIF function in order to get all of the true 
  values
  separated by ","

  thank's
  /kostas