Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Address Expression (Access 11)

    I have 3 fields Title, Forename(s) & Surname from which I use an expression to address a letter. It works fine of most instances. I use a function to extract initials from the Forename(s) field.

    For example:-

    Mr & Mrs John & Jane Smith

    turns into

    Mr & Mrs J & J Smith

    A problem is an unmarried couple. For example: -

    Mr & Miss John & Jane Smith & Jones

    turns into

    Mr & Miss J & J Smith & Jones

    which is clunky

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Address Expression (Access 11)

    How does Mr & Miss John & Jane Smith & Jones parse into the fields? Is it this?

    Mr & Miss | John & Jane | Smith & Jones

    If so, I can't think of any easy, reliable way to handle it . Even if you know it will be structured correctly, how do you expect to handle Miss & Miss Joan & Jane Smith? Any time you put two pieces of data in the same field (i.e., John & Jane) you not only compromise the relational nature of your data, but you create hideous complications in using it.
    Charlotte

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

    Re: Address Expression (Access 11)

    As Charlotte pointed out, the problem has no satisfactory solution. Perhaps a query with the following SQL will be acceptable:

    SELECT InStr([Title],"&") AS TitlePos, InStr([Forename],"&") AS ForenamePos, InStr([Surname],"&") AS SurnamePos, IIf([TitlePos],Trim(Left([Title],[TitlePos]-1)),[Title]) AS Title1, Trim(Mid([Title],[TitlePos]+1)) AS Title2, Left([Forename],1) AS Initial1, Left(Trim(Mid([Forename],[ForenamePos]+1)),1) AS Initial2, IIf([SurnamePos],Trim(Left([Surname],[SurnamePos]-1)),[Surname]) AS Surname1, Trim(Mid([Surname],[SurnamePos]+1)) AS Surname2, IIf([ForenamePos],[Initial1] & " & " & [Initial2],[Initial1]) AS FullInitials, IIf([SurnamePos],[Title1] & " " & [Initial1] & " " & [Surname1] & " & " & [Title2] & " " & [Initial2] & " " & [Surname2],[Title] & " " & [FullInitials] & " " & [Surname]) AS FullName
    FROM tblNames;

    where tblNames is the name of the table.

    <table border=1><td align=center>Title</td><td align=center>Forename</td><td align=center>Surname</td><td align=center>FullName</td><td>Mr</td><td>John</td><td>Jones</td><td>Mr J Jones</td><td>Mr & Mrs</td><td>John & Jane</td><td>Jones</td><td>Mr & Mrs J & J Jones</td><td>Mr & Miss</td><td>John & Jane</td><td>Jones & Smith</td><td>Mr J Jones & Miss J Smith</td><td>Miss & Miss</td><td>Joan & Jane</td><td>Jones & Smith</td><td>Miss J Jones & Miss J Smith</td><td>Miss & Miss</td><td>Joan & Jane</td><td>Jones</td><td>Miss & Miss J & J Jones</td><td>Mr & Mr</td><td>John & Jim</td><td>Jones</td><td>Mr & Mr J & J Jones</td><td>Mr & Mr</td><td>John & Jim</td><td>Jones & Smith</td><td>Mr J Jones & Mr J Smith</td></table>
    If you want more intelligent parsing, it would be better to write a VBA function - doing everything in the query would become very complicated.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Address Expression (Access 11)

    If you want to get technical, Mr & Mrs John & Jane Smith is incorrect. I believe it would be either Mr & Mrs John Smith or John & Jane Smith, unless they've changed the etiquette since the last time I looked.
    Charlotte

  5. #5
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Address Expression (Access 11)

    I fully accept all your comments. I understand the faults in the way I have structured the data but I was young then...... It reminds me of the old Irish joke along the lines of 'How do I get to Dublin?' 'I wouldn't start from here!'
    Hans solution is perfect. Just what I was striving for before I got lost in the complexity of writing an expression. Why I didn't think of using a seperate query I'll never know.

    Thanks to you both for your interest & help.

  6. #6
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Address Expression (Access 11)

    You Americans are such sticklers for etiquette! Well I asked the Queen at dinner last night & she said you were technically correct but what I wanted to do was fine! PS the Duke of Edinburgh agreed too.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Address Expression (Access 11)

    We are NOT amused! <img src=/S/queen.gif border=0 alt=queen width=19 height=20> <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    Charlotte

Posting Permissions

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