Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2004
    Posts
    46

    Unanswered: Single line addresses convert to paragraph style addresses

    I've got addresses as such:

    Address 1
    Address 2
    Address 3
    Post Code
    Town or City


    Now this is obviously the best way to arrange addresses as we'll be using them in mail shots.

    Problem is that some addresses are quite large and including things like "Abbey House" "Unit 123 Balliol Business Centre"

    The problem arises when I use them in a report. Some will have blank spaces as each field is laid out underneath one another.

    How do I get the addresses to appear like the following but only using one field which is set to "Allow grow":

    Mr Wilson
    123 Ayton Street
    Lambton
    Sunderland

    I've managed to get the fields to appear joined with seperate names using the Expr method for example: Expr3: [Client_First_Name]+" "+[Client_Last_name]

    Is there a way of modifying an address query to make the addresses appear all in one paragraph format taking into account missing lines.

    Using one fi

  2. #2
    Join Date
    Nov 2004
    Posts
    32
    Base your report on a query. In the query format the string for the address. Concatonate the address lines into one field, use the IIF function to replace an address line with nothing if it is null (or empty).

    You'll need a carriage return to create new lines in the query. Create your own function to replace the CR as queries don't ake this.


  3. #3
    Join Date
    Nov 2004
    Posts
    64
    This might help

    Me![Address6] = IIf(IsNull(Me![Address1]), "", Me![Address1] & vbNewLine) & IIf(IsNull(Me![Address2]), "", Me![Address2] & vbNewLine) & IIf(IsNull(Me![Address3]), "", Me![Address3] & vbNewLine) & IIf(IsNull(Me![Address4]), "", Me![Address4])

    cheers

    Peter

  4. #4
    Join Date
    Dec 2004
    Posts
    46
    Thanks Derrick and Peter.

    Peter, where would I place this code?

  5. #5
    Join Date
    Nov 2004
    Posts
    64
    It would need something to trigger it, ie in the form, "on activate", command button "onclick", textbox "after update", on enter, on got focus, on mouse down ,etc etc
    unfortunately it wouldn’t work in a query

    good luck

    Peter

Posting Permissions

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