Concatenating table fields and checking for null values 2

[Watermelon]

Hi,

Can anyone help me with this query? I'm trying to update a field in one table by setting it equal to a series of concatenated fields in the same table. I'd like to separarate each concatenated field with a comma only if the field is not empty and has some value in it.

Here's the code I have so far:

UPDATE Table1 SET Table1.Field1 = Table1.Field2 & ", " & Table1.Field3 & ", " & Table1.Field4;

Again, I'd only like these commas to be updated into field1 if the fields have some value in them.

Thanks,
Watermelon

 

[LittleSmudge]

Before the solution - the caveat
You should not be duplicating data by storing concatenated collections of data from multiple fields into one field. This is seriously BAD practice.

If you need this info on a Form or Report then calculate it at the time that it get displayed ( printed ) .


However, on the basis that you either
A) have a very good need to do it this way
or
B) you're going to ignore the advice and do it anyway

here is a solution.

UPDATE Table1 SET Table1.Field1 = Nz(Table1.Field2,"&quot & Iif(IsNull(Table1.Field2),"",", &quot & Nz(Table1.Field3,"&quot & Iif(IsNull(Table1.Field3),"",", &quot & Nz(Table1.Field4,""



'ope-that-'elps.

G LS

 

[BlackKnight]

Hi,

Try this update query

UPDATE tblTable SET tblTable.Fld1 = Mid$(IIf(IsNull([Fld2]),"",[Fld2] & ",&quot & IIf(IsNull([Fld3]),"",[Fld3] & ",&quot & IIf(IsNull([Fld4]),"",[Fld4] & ",&quot & IIf(IsNull([Fld5]),"",[Fld5] & ",&quot & IIf(IsNull([Fld6]),"",[Fld6] & ",&quot,1,Len(IIf(IsNull([Fld2]),"",[Fld2] & ",&quot & IIf(IsNull([Fld3]),"",[Fld3] & ",&quot & IIf(IsNull([Fld4]),"",[Fld4] & ",&quot & IIf(IsNull([Fld5]),"",[Fld5] & ",&quot & IIf(IsNull([Fld6]),"",[Fld6] & ",&quot)-1);

Have a good one!
BK

 

[Watermelon]

Hi guys,

Thanks for the feedback. I'm doing this only in a temp table. It's kind of a long story but that's why I'm duplicating this data before moving it to a permanent table.

Thanks to both of you. Both suggestions worked.

Watermelon