Results 1 to 5 of 5
  1. #1
    Iron_Horse is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Location
    Sussex, UK
    Posts
    5

    Question [Address] Creating a clean looking Address Block in a Report from multiple fields, with no blank row

    Thanks in advance!


    I've seen various versions and responses for parts of this very common issue.
    I've spent hours on this, so before I spend DAYS working out the code to solve it, has anyone else got a nice finished version they'd like to point me to or share?

    Creating an address block, concatenating multiple fields, and leaving out blank rows.

    Table fields
    [PO1 ] '/ 1= House name
    [PO2] '/ 2= StreetAdd
    etc '/ 3 = Village
    '/ 4 =Town
    '/ 5 = City or County
    '/ 6 = Postal Code
    Report Field: [addressblock]
    vba to concatenate: [PO1] & (", " + [PO2] & " " & [PO3] etc ...
    and then place, with relevant linebreaks (vbcrlf), in target [addressblock] field on report [Rpt1].
    That's the easy bit
    But IF PO4 is blank, I don't want to put in the commas and vbcrlf
    It changes every time
    so final address block could be any combination such as
    [PO1] '/ 1= House name
    [PO3] '/ 3 = Village
    [PO5] '/ 5 = City or County
    [PO6] '/6 = Postal Code
    or
    [PO2] '/2= StreetAdd
    [PO5] '/ 5 = City or County
    [PO6] '/6 = Postal Code
    I don't want blankrows between each string.
    To complicate it, my blank fields are not Null or Empty,
    They return a Len of 0,
    but on query iif(len[etc] <> 0, "Not Empty", "Empty" ) it returns a blank (while a known null produces "Empty" as it should).

    I'm still struggling with that bit, my next guess is to play with "trim" to create a zero length string?

    =IIf((Len(Trim([PO4] & ""))<1)," it's empty","' it's not empty") identifies it as an empty

    Does anyone have a pre-existing and neat solution ?
    (or point to where it's been answered fully here)


    Cheers


  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Search is Sample Databases for Address, I saw a number of items in there that might be what you need.

  3. #3
    Iron_Horse is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Location
    Sussex, UK
    Posts
    5
    Thanks Bulzie,
    Much appreciated.

    In the meantime I went back to basics and simply created a text box in the Report and built an expression that does the job:
    (I think I was trying to over-engineer it )

    Code:
    '/ Control Expression in an unbound text box on report: (using CtrlEnter within " " to generate line breaks)
    
    =IIf((Trim([POAddressLine1])=""),"",(+[POAddressLine1]+","+"
    ")) & IIf((Trim([POAddressLine2])=""),"",(+[POAddressLine2]+","+"
    ")) & IIf((Trim([POAddressLine3])=""),"",(+Trim([POAddressLine3])+","+" 
    ")) & IIf((Trim([POAddressLine4])=""),"",(+[POAddressLine4]+","+"
    ")) & IIf((Trim([POCity])=""),"",(+[POCity]+","+"
    ")) & IIf((Trim([PORegion])=""),"",(+[PORegion]+"," & "       " & [POPostalCode]))
    Thanks
    Last edited by Iron_Horse; 05-12-2021 at 06:17 AM.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Here's something I use with downloaded JSON address data for UK postcodes from a provider called Ideal Postcodes
    The field names are slightly different but the principle is the same

    Code:
    AddressLine = IIf(OrganisationName <> "", OrganisationName & ", ", "") & _                
                    IIf(SubBuildingname <> "", SubBuildingname & ", ", "") & _
                    IIf(Buildingname <> "", Buildingname & ", ", IIf(BuildingNumber <> "", BuildingNumber & " ", "")) & _
                    Thoroughfare & ", " & IIf(DependantLocality <> "", DependantLocality & ", ", "") & _
                    PostTown & ", " & County & ", " & Country & ", " & POSTCODE
    
    
     If Left(AddressLine, 1) = "," Then AddressLine = Mid(AddressLine, 3)
          
     AddressBlock = Replace(AddressLine, ", ", vbCrLf)
    As you can see, I create both an address line and an address block.
    You can skip the AddressLine if you wish
    I don't use a comma at the end of each line in an address block
    IIf statements are used for fields which may or may not exist in a particular address e.g. BuildingName
    Other fields such as PostTown & Postcode ALWAYS exist so these don't need IIf statements.
    Last edited by isladogs; 05-12-2021 at 06:33 AM.
    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

  5. #5
    Iron_Horse is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Location
    Sussex, UK
    Posts
    5
    Nice one Colin @isladogs

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

Similar Threads

  1. Replies: 4
    Last Post: 01-06-2018, 03:26 PM
  2. Replies: 7
    Last Post: 06-27-2016, 12:28 PM
  3. Using an Address Block Module for a report
    By mcgeester in forum Reports
    Replies: 11
    Last Post: 08-04-2014, 01:12 PM
  4. Replies: 2
    Last Post: 08-22-2013, 12:02 AM
  5. Input Mask for an IP Address and Mack Address
    By baksg1995 in forum Access
    Replies: 18
    Last Post: 06-23-2009, 12:33 PM

Tags for this Thread

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