Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IIF syntax (A2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IIF syntax (A2000)

    A blank value is not "", but Null. So the test <code>[tblOrg.strOrgName]=""</code> is never True. You test for something being Null by using IsNull: <code>IsNull([tblOrg.strOrgName])</code>. 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.

    <code>=[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])</code>

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF syntax (A2000)

    What an improvement!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •