Address Expression (Access 11)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Title	Forename	Surname	FullName
Mr	John	Jones	Mr J Jones
Mr & Mrs	John & Jane	Jones	Mr & Mrs J & J Jones
Mr & Miss	John & Jane	Jones & Smith	Mr J Jones & Miss J Smith
Miss & Miss	Joan & Jane	Jones & Smith	Miss J Jones & Miss J Smith
Miss & Miss	Joan & Jane	Jones	Miss & Miss J & J Jones
Mr & Mr	John & Jim	Jones	Mr & Mr J & J Jones
Mr & Mr	John & Jim	Jones & Smith	Mr J Jones & Mr J Smith

If you want more intelligent parsing, it would be better to write a VBA function – doing everything in the query would become very complicated.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

We are NOT amused!

Reply | Quote