Query expression problem

[rbrule]

Hello,
I have four fields in a table with the same type of data that I want to combine into one. As a result I have set up a calculated field in a query. Some fields in the table are null and some are a zero length string. I used the following expression, and variations thereof, but it doesn't work. Could someone tell me what I did wrong?

IIf([field1]is null or ="",[field2],IIf([field1]and[field2]is null or ="",[field3],IIf([field1]and[field2]and[field3]is null or ="",[field4],[field1])))

 

[DBL]

Hello,
I have four fields in a table with the same type of data that I want to combine into one. As a result I have set up a calculated field in a query. Some fields in the table are null and some are a zero length string. I used the following expression, and variations thereof, but it doesn't work. Could someone tell me what I did wrong?

IIf([field1]is null or ="",[field2],IIf([field1]and[field2]is null or ="",[field3],IIf([field1]and[field2]and[field3]is null or ="",[field4],[field1])))

You could try

IIf (isNull ([field1]),"",[field1]) & IIf (isNull ([field2]),"",[field2]) & IIf (isNull ([field3]),"",[field3]) & IIf (isNull ([field4]),"",[field4])

 

[rbrule]

You could try

IIf (isNull ([field1]),"",[field1]) & IIf (isNull ([field2]),"",[field2]) & IIf (isNull ([field3]),"",[field3]) & IIf (isNull ([field4]),"",[field4])

Hi DBL, thank you for your response. I tried the above expression, and I am still having a problem. The expression returns the contents of each field to the calculated field. If [field one] [field Two] [field three]and [field four] each have contents, then the contents are also copied to the calculated field, so I basically have the contents of four cells placed into one.

What I need it to do is copy the contents of field one to the calculated field if there are any, or copy the contents of field two to the calculated field, if field one is null or contains a zero length string, or copy the contents of field three to the calculated field if fields one and two are null or contain a zero length string, or copy the contents of field four to the calculated field if fields one, two, and three are null or contain a zero length string.

Is there something I can add to the above expression to correct the problem?