Results 1 to 13 of 13
  1. #1
    Robert Gillard is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    3

    Remove blank line in address

    A customer input form allows for up to 3 address lines to be added plus a post code. The address label has been created to match this parameter, however on some occasions only 2 address lines (plus a post code) are used. This results in a blank line appearing above the post code.

    In short, when only 2 address lines are used, i am looking to say if [Ad3] is blank then move up [PostCode] ... whilst at the same time ensuring that the post code is not then repeated on both lines


    =Trim([Ad2] & ".")
    =Trim([Ad3] & ".")


    =Trim([PostCode] & ".")


    any assistance gratefully received

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Post the code that creates the label text - or is that it? If you're using vba, you might be able to use carriage returns and line feeds, but only for lines that contain anything.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Try using the Len function

    Code:
    IIf Len([Ad3]>0,Trim([Ad3] & "."),"")
    I'd also suggest using one expression for all 3 lines with line returns
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    depends on layout, but setting the controls to 'can shrink' will remove the blank lines

  5. #5
    Robert Gillard is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    3
    These labels are generated just from using “Create”, then “Labels”, from there you just select the fields (from the table) you wish to use. It then drops those fields into the “detail” section of a Report which it creates.
    From there just right click on the Report and “print Labels” is an option. There does not appear to be any other coding, apart from which I have detailed above.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Never have used that. Tried it and Can Shrink seems to take care of it, but that's the default property; meaning it should work for you. I tested with zls ("") values and still works.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    How about Law of Propagating Nulls - Anything plus a null is null.

    Code:
    (StreetAddress) &  (vbNewLine + AddressLine2) & (vbnewline + City) & (vbNewLine + State) & (vbNewLine + Zip)

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    moke123:
    Won't that create a blank line where there isn't a value? I thought OP wanted no blank lines.

  9. #9
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    no, a null plus anything is null.

    you may have to play around with constructing it but using a plus (+) inside the the () instead of using a "&" yields a null if either side of the equation is null.

    I use it a lot constructing names - (FName + " ") & (MName + " ") & (LName) & (" " + suffix). this keeps the spaces correct if, for instance, theres no middle name.

    this works
    Code:
    str = str & (Me.street + vbNewLine)
    str = str & (Me.street2 + vbNewLine)  'if theres no street2 this line resolves to str = str & null, so no line or line feed
    str = str & (Me.city + vbNewLine)
    str = str & (Me.state + vbNewLine)
    heres a longer explanation... https://www.utteraccess.com/wiki/Nul...Their_Behavior

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You used wizard to create a report designed to accommodate spacing of a particular size label. You can edit it same as any other report but then don't expect records to print properly to a sheet of labels.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I tested and see that you are correct. I figured there would be a line wrap in spite of the + Null.
    June7: reasonable assumption but apparently not the case. Must be the page setup that keeps things aligned but it's late and I'm not going to look into it now.
    I shortened the control widths and added borders to make it easier to see the effect of nulls. Top field is date, middle is text, last is text, some of which contain digits.
    Click image for larger version. 

Name:	labelsReport.jpg 
Views:	20 
Size:	17.0 KB 
ID:	40429
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, as long as edits don't modify page setup. Label size is defined by Detail section dimensions and column settings determine number of columns and spacing between records.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Robert Gillard is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    3
    Big thanks to moke123, his suggestion of a "+" instead of a "&" has sorted it perfectly.

    =Trim([Ad1] & ","
    =Trim([Ad2] & ",")
    =Trim([Ad3]+".")
    =Trim([PostCode] & ".")

    So now when Address line 3 [Ad3] is blank, [PostCode] moves up automatically to become the new 3rd line ... with no blanks anywhere.
    Thank you all, your help and assistance has been much appreciated.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Remove Error Line from Report
    By AdiDoyle in forum Access
    Replies: 15
    Last Post: 08-08-2017, 02:23 PM
  2. Replies: 4
    Last Post: 04-03-2015, 05:53 PM
  3. Replies: 3
    Last Post: 04-16-2014, 06:03 PM
  4. Replies: 5
    Last Post: 12-06-2010, 10:15 AM
  5. how to validate the first line of address
    By dunners92 in forum Access
    Replies: 1
    Last Post: 03-15-2010, 09:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums