[AccessD] how to use IIF for six different fields

    Thanks for the kind words Stuart! 

    Your subsequent suggestion, applying Nz() function over the whole expression, would ensure that the result is always a string.

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

  ----- Original Message ----- 
  From: Stuart McLachlan 
  To: Access Developers discussion and problem solving 
  Sent: Monday, June 08, 2009 14:11
  Subject: Re: [AccessD] how to use IIF for six different fields


  Very clever.
  That's another neat hack for my toolbox.
  -- 
  Stuart

  On 8 Jun 2009 at 10:32, A.D.Tejpal wrote:

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