[AccessD] how to use IIF for six different fields

    Thanks for the clarification Steve! I agree. In fact, during my tests before posting the suggested solution, I did verify safe behavior of Mid() function for Null as well as zero length string.

    Stuart's suggestion to apply Nz() function over the whole expression would ensure that the result would always be a string.

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

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


  A.D.

  --------------------------------------------------
  > On 8 Jun 2009 at 10:32, A.D.Tejpal wrote:
  > ...
  >   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.
  > ====================================
  > SELECT T_Data.*, Mid((","+[F1]) & (","+[F2]) & (","+[F3]) & (","+[F4]) & 
  > (","+[F5]) & (","+[F6]),2) AS MergedValue
  > FROM T_Data;
  > ====================================

  Just to clarify, as the expression in this example would be Null if all 6 
  fields were.  So, in addition to what you stated, we should also say that 
  the Mid() function returns Null, without error, in such a case.
  -- 
  Regards
  Steve