[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;
> ====================================
> 
>   ----- 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
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com