[AccessD] how to use IIF for six different fields

Hi A.D. et al

Just have in mind that wrapping with Nz - when used in the example SQL code - may not be what you want ... all fields having Null should most often return a Null.

/gustav


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