Results 1 to 4 of 4
  1. #1
    Join Date
    May 2002
    Location
    On the edge
    Posts
    11

    Question Unanswered: Empty Spaces and Blank Lines and a long IIF

    Hi All,
    I've seen this issue covered to some extent in previous posts, but here's my "special case" sob story:

    Sometimes, address fields are blank (like no country information) and I want to move up the next field on the report so I don't print blank lines. I've concatenated my address fields, checked them for nulls and it doesn't work. I thought it was because my 'blank' fields are actually comprised of spaces (38 spaces), so I tried trimming them with Trim and Trim$, but that didn't work either (see code sample below). I tried putting in 38 spaces between quote marks, but that didn't work either - I may have miscounted as my thumb got very tired...

    I'm running Access 2002 on Win98. Yes, I've read Q287685 Concatenating Fields in Text Box to Remove Blank Lines.

    Here's my code with the trim$ functions intact (hope it shows okay):

    =IIf(IsNull(Trim$([CUST])),"",([CUST]) & Chr(13) & Chr(10)) & IIf(IsNull(Trim$([cNAME])),"",([cNAME]) & Chr(13) & Chr(10)) & IIf(IsNull(Trim$([BILL_ADDR_1])),"",([BILL_ADDR_1]) & Chr(13) & Chr(10)) & IIf(IsNull(Trim$([BILL_ADDR_2])),"",([BILL_ADDR_2]) & Chr(13) & Chr(10)) & IIf(IsNull(Trim$([BILL_ADDR_3])),"",([BILL_ADDR_3]) & Chr(13) & Chr(10)) & IIf(IsNull(Trim$([BILL_CITY])),"",([BILL_CITY]) & " " & [BILL_STATE] & " " & [BILL_ZIP] & Chr(13) & Chr(10)) & IIf(IsNull(Trim$([BILL_COUNTRY])),"",([BILL_COUNTRY]) & Chr(13) & Chr(10)) & IIf(IsNull(Trim$([BILL_CONTACT])),"",([BILL_CONTACT]) & Chr(13) & Chr(10))

    What the heck am I missing?
    Love the forum...Have a great long weekend (it's Holidays on our side of the puddle) if I don't hear from y'all--
    Thanks,
    Zsoka

  2. #2
    Join Date
    May 2002
    Posts
    4
    One simple option, is to go to the properties of the field, and click "yes" for the "Can Shrink" property.

    However, if you have a label for the field, it will only shrink to the that field.

  3. #3
    Join Date
    May 2002
    Location
    On the edge
    Posts
    11

    Empty Spaces...

    Thanks for your speedy reply, but the CanShrink is already set to Yes and yet the field does not shrink. FYI - there is no label associated with this concatenated field.
    Thanks though.
    Any more ideas, let me know...I did try an IsEmpty expression, but that didn't work, either.

  4. #4
    Join Date
    May 2002
    Location
    On the edge
    Posts
    11

    I figured it out!

    For anyone else out there having similar problems, here is the code I used which eventually worked:


    =IIf(Trim([CUST])="","",([CUST]) & Chr(13) & Chr(10)) & IIf(Trim([cNAME])="","",([cNAME]) & Chr(13) & Chr(10)) & IIf(Trim([BILL_ADDR_1])="","",([BILL_ADDR_1]) & Chr(13) & Chr(10)) & IIf(Trim([BILL_ADDR_2])="","",([BILL_ADDR_2]) & Chr(13) & Chr(10)) & IIf(Trim([BILL_ADDR_3])="","",([BILL_ADDR_3]) & Chr(13) & Chr(10)) & IIf(Trim([BILL_CITY])="","",([BILL_CITY]) & " " & [BILL_STATE] & " " & [BILL_ZIP] & Chr(13) & Chr(10)) & IIf(Trim([BILL_COUNTRY])="","",([BILL_COUNTRY]) & Chr(13) & Chr(10)) & IIf(Trim([BILL_CONTACT])="","",([BILL_CONTACT]) & Chr(13) & Chr(10))

    Zsoka


Posting Permissions

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