field concatenation on a form

[theosgood]

I am building a form in Access 2007. I have 5 fields that when concatenated populate a 6th field, Offer Desc.

=IIf([Item Promo Price]=0,IIf([Category Discount]=0,Format([DollarOff],"Currency"),Format([Category Discount],"0%")),Format([Item Promo Price],"Currency"))+"; "+[Gender]+" "+[VendorName]+" "+[ModelNumber]+" "+[ItemDesc]

This works perfectly if all fields are populated, if any one of the fields is null, then Offer Desc is null.

I tried IIf([ModelName]=Null," ",[ModelNumber]). I added a sub to each of the 5 fields to refresh on lost focus. I've tried setting the default value for the fields to " ". No luck. Suggestions please.

[pbaldy]

You're running into a handy quirk that can be used to your advantage. + as a concatenation operator propogates Null's; & does not.

[theosgood]

Thanks Paul. That solved the problem. Nice little concatenation tidbit to store away.

[pbaldy]

No problem. & is the "standard" concatenation operator, but creative use of + can be helpful when you only want to include one part when another part isn't Null.