Adding a iif is null formula to my query

[Barry83]

Hi I am doing a concatenation query in access using Builder. when I run my query it brings back a cell with just the ;; in it as the row before obviously doesnt have any data in it. I want to incorporate an iif function and to look at if the cell before is null then don't include in the concatenation. Can you assist in how to add into my function below:

Expr1: [Business rule 1] & ";" & [Business rule 2] & ";" & [Business rule 3]

 

[pr2-eugin]

What you need is called as Null propagation, there are plenty of articles on this. Here is a MSDN Article on this topic.

Hope that helps ! Good Luck.

 

[Barry83]

Hi Paul, thanks for your help. I tried that function but it didnt quite return the data concatenated the way i was hoping for as there is loads of blanks which are sporadic throughout the cells and many in the first cell. I tried this function but again it as given me a syntax error (comma) in expression. Any ideas?

Expr1: IIf(IsNull([Business rule 1]),"",[Business rule 1]) & (IsNull([Business rule 2]),"",[Business rule 2]) & (IsNull([Business rule 3]),"",[Business rule 3])

cheers
barry

 

[CazB]

If you want the separators, would it not be

=[Business rule 1] & iif(isnull([Business rule 1]),"", ";") & [Business rule 2] & iif(isnull([Business rule 2]),"", ";") & [Business rule 3]

?