Access concatenated string blank if any of the fields are blank

LindaOKSTATE
LindaOKSTATE used Ask the Experts™
on
I have 3 fields, COMMENT_1, COMMENT_2 and COMMENT_3.  Almost every record has a  COMMENT_1, a few have  COMMENT_1 & COMMENT_2 and of course even less have  COMMENT_1 & COMMENT_2  & COMMENT_3.   I have concatenated them in the query as Comments: [ COMMENT_1] & " "& [ COMMENT_2]& " "& [COMMENT_3].  If all three comment fields are not blank, the concatenated string shows up in the query.  If   COMMENT_2 or COMMENT_3 are blank, none of the comments show up and the concatenated field is blank.  Can anyone see what is wrong?  I have never had this happen before.

Thanks
Linda
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
You may have to incorporate IIF statements to handle the blank fields.

Comments: IIf(IsNull([COMMENT_1]),"",[COMMENT_1] & " ") & IIf(IsNull([COMMENT_2]),"",[COMMENT_2] & " ") & IIf(IsNull([COMMENT_3]),"",[COMMENT_3]).
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
Also try this:


Comments: [ COMMENT_1] + " " + [ COMMENT_2] + " " + [COMMENT_3]

Author

Commented:
I tried Comments: [ COMMENT_1] + " " + [ COMMENT_2] + " " + [COMMENT_3] .  It did what my original statement did.  If any fields were blank, it made the whole statement blank.  However, since my original statement is working now, I'm not really sure what my Access is doing.

Thank you for your suggestion.
Linda
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
I'm not too surprised your original statement started working.


A null concatenated with a string will result in that string, so I don't think there was anything wrong with your syntax.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial