[AccessD] how to use IIF for six different fields

    Thanks for the kind words Gustav!  As always, you are so supportive.

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

  ----- Original Message ----- 
  From: Gustav Brock 
  To: accessd at databaseadvisors.com 
  Sent: Monday, June 08, 2009 14:43
  Subject: Re: [AccessD] how to use IIF for six different fields


  Hi A. D.

  That's an old trick - using + and Null - but I've never found out how to get rid of the "first" separator and was saved by the fact that where I've had a need to concatenate fields/variants this way the first field would always have a content. Thanks for this neat and generic solution!

  /gustav


  >>> adtp at airtelmail.in 08-06-2009 07:02 >>>
  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;
  ====================================