IIF syntax (A2000)

Topic

New Reply

I’m working on an address block for an invoice. I’ve created an unbound text box with the expression below as control source. I want the IFF to do is include fields which have a value and go to a new line. What I’m getting now is a blank line if the field has no value. I can’t see what I’m doing wrong in IIF statement. Anyone else see it?

=[strFName] & ” ” & [strLName] & Chr(13) & Chr(10) & IIf([tblOrg.strOrgName]=””,””,[tblOrg.strOrgName] & Chr(13) & Chr(10)) & IIf([tlkpAddrShp.strPOBox]=””,””,[tlkpAddrShp.strPOBox] & Chr(13) & Chr(10)) & IIf([tlkpAddrShp.strAddrPrt1]=””,””,[tlkpAddrShp.strAddrPrt1] & Chr(13) & Chr(10)) & IIf([tlkpAddrShp.strAddrPrt2]=””,””,[tlkpAddrShp.strAddrPrt2] & Chr(13) & Chr(10))
….there more but it’s just more of the same….

E

Reply | Quote

Replies

A blank value is not “”, but Null. So the test [tblOrg.strOrgName]="" is never True. You test for something being Null by using IsNull: IsNull([tblOrg.strOrgName]). But your expression has another problem: it will always end with a blank line. To avoid this, I would switch the tests around. I usually apply a little trick: concatenating with + instead of with & results in Null if one of the components is Null.

=[strFName] & " " & [strLName] & ((Chr(13) & Chr(10))+[tblOrg.strOrgName]) & ((Chr(13) & Chr(10))+[tlkpAddrShp.strPOBox]) & ((Chr(13) & Chr(10))+[tlkpAddrShp.strAddrPrt1]) & ((Chr(13) & Chr(10))+[tlkpAddrShp.strAddrPrt2])

Reply | Quote

What an improvement!

Reply | Quote

What an improvement!

Reply | Quote

A blank value is not “”, but Null. So the test [tblOrg.strOrgName]="" is never True. You test for something being Null by using IsNull: IsNull([tblOrg.strOrgName]). But your expression has another problem: it will always end with a blank line. To avoid this, I would switch the tests around. I usually apply a little trick: concatenating with + instead of with & results in Null if one of the components is Null.

=[strFName] & " " & [strLName] & ((Chr(13) & Chr(10))+[tblOrg.strOrgName]) & ((Chr(13) & Chr(10))+[tlkpAddrShp.strPOBox]) & ((Chr(13) & Chr(10))+[tlkpAddrShp.strAddrPrt1]) & ((Chr(13) & Chr(10))+[tlkpAddrShp.strAddrPrt2])

Reply | Quote