[AccessD] how to use IIF for six different fields

Gustav Brock Gustav at cactus.dk
Mon Jun 8 04:13:38 CDT 2009


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

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





More information about the AccessD mailing list