Concatenation

[Rogeman]

Hello, I am attempting my first database in access 2003. It is for a group membership.
All has gone well until I tried to concatenate from a query and save to a table,so far so good, but this
is what I have come up against and am unable to solve.

The following gave me this result when creating postal labels:-
POSTAL NAME: Left([FORENAME_Male],1) & " " & Left([FORENAME_Female],1) & " " & ([surname])

Mr John Smith ...................................Mr J Smith. (Ok for single male)
Mrs Mary Smith...................................Mrs M smith. (Ok for single female)
Mr & Mrs David and Anne Evans.............Mr & Mrs D A Evans. ("&" Missing between D and A)


The following gave me this result when creating postal labels:-
POSTAL NAME: Left([FORENAME_Male],1) & " & " & Left([FORENAME_Female],1) & " & " & ([surname])

Mr John Smith ...................................Mr & J Smith. ("&" not neede for single male)
Mrs Mary Smith..................................Mrs & M smith. ("&" not needed for single female)
Mr & Mrs David and Anne Evans.............Mr & Mrs D & A Evans. ("&" needed between D and A)

How do I remove the unwanted "&", in the single person case but keep it for couples?

The Title bit is from a column "Title".
The Couples are entered in the same entry with common membership number.
Singles have their own membership number.

Thanks in advance.

Roger

 

[jdraw]

You do not need to save this concatenation to a table.
A table should contain information about a single subject. The fields in the tables should be atomic ( not things like city state and zip in a single field). Atomic values give you the most flexibility for using the data.

You can create labels with a Report. You can use a query as the record source of that report.

see http://office.microsoft.com/en-us/access-help/create-mailing-labels-in-access-HA010341599.aspx

 

[Rogeman]

Hi Thanks for your reply.
Perhaps I should ask the question in a different way. I have the male/female fornames in one column each in the table, entered from a Form. What I think I want to happen is that the Initials are pulled out from the forenames columns, I can then enter that field in the Postal Report. I still have the original problem of how to not put the "&" In the field.Where should I best do this and how? The postal reports I can handle, I use different Criteria depending what I want printed.

Regards
Roger

 

[Rogeman]

Hi Jack,
I am still not achieving my goal, I enclose a view of my Query that I have showing my results.
This is the code I am using :-
Expr5: [title] & " " & Left([FORENAME_M],1) & " & " & Left([FORENAME_F],1) & " " & ([surname])
As you will see I still have an unwanted "&" before the female Initial.
Any suggestions Would be appreciated.

Roger

 

[Brianwarnock]

Pat I don't see how your suggestion can work as it forces 2 & to be inserted.

Personally I think that it will require a function to be written as the & are dependent on whether data exists.

Brian

 

[Brianwarnock]

Try this

Function postaladdress(title, mname, fname, sname) As Variant

If Not IsNull(mname) And Not IsNull(fname) Then
       postaladdress = title & " " & Left(mname, 1) & " & " & Left(fname, 1) & " " & sname
ElseIf IsNull(mname) Then
       postaladdress = title & " " & Left(fname, 1) & " " & sname
Else
       postaladdress = title & " " & Left(mname, 1) & " " & sname
End If

End Function

 

[Rogeman]

Thanks Brian,

Your function did the trick, I did not understand how to create functions, but I think I do now,anyway when I inserted it in my query it gave the required results.


Roger

 

[Brianwarnock]

If you have had nothing to do with user defined functions before you did well to use my function without any explanation as to how, I just assumed it would not be a problem, and I guess it wasn't.

Brian

 

[Rogeman]

Hi Brian,
A good look in the Help files and internet got me there in the end. I used your function in my Query an had the result I wanted, but got too adventurous and tried to adapt it and insert it in the same Query to give me just the Initials in another field, but no luck yet.
This was just an exercise rather than a real need.

anyway Thanks a lot for your help

Roger

 

[Brianwarnock]

Hi Pat I thought that that must be what was expected but it did not work when I tested your formula, I must have had a ZLS . If you delete the contents of a field are you left with a NULL OR ZLS.? I'll have to check.

Brian

 

[Brianwarnock]

I used the delete key so I will take a look tomorrow, it's getting late here now, well for this old guy.

Brian

 

[Brianwarnock]

I tried it again and still had the problem , then realised that it was in a situation that you had not shown, no male forename but a female forename. as long as you have a female forename you will get & name
Thus Miss & E Smith

I don't see how that can be avoided.

Brian

 

[Brianwarnock]

I feel sure that I tried everything but anything in " " always appears if there is a female forename.

Brian

 

[Brianwarnock]

Did you try replacing the first & with a +?

This

POSTAL NAME: [title] & " " & Left([FORENAME_Male],1)+" & "+Left([FORENAME_Female],1) & " "+([surname])

omits ALL of the initials including the FORENAME_MALE unless both exist when everything is ok.
I cannot get any combination to work for all scenarios , so I will recommend my function.

Brian

 

[Brianwarnock]

HMM! OK I know this is just out of curiosity as the poster is happy but I guess I'll just have to retest when I get a bit of time.

Brian

 

[jdraw]

Brian,

I read Pat's post to mean, and this is my interpretation,
if the function solution works, then go for it.

I didn't interpret her post as her approach worked in all the test cases you set up.

Anyway, have a great day.

 

[Brianwarnock]

Oh! I hadn't thought of that interpretation.
As I cannot get the formulae to work for all cases I think I will quit now.

Cheers
Brian

 

[Brianwarnock]

Thanks for that Pat, but I still cannot get it to work. I've come to the conclusion that either I am thick or its an Access release issue.
The variety of results I got when varying the & and + kind of made sense but to get Mr & Mrs B & E Smith meant that I always got Mrs & E Smith or no initials for Male or Female if one was missing.

I realise that you have more to do than old this retired widower but if you do have a spare nanosecond you might care to look at my little test DB and show this old guy how to do it.

No worries if you don't.

Brian

 

[Rogeman]

Hi All,

Thanks for all the different answers to what I thought was a simple question, I did not realise that it would be such a complex task.
Should I conclude that most people just create postal labels manually in their reports rather than get Access to do it for them.
I am a great believer in automating everything (thats what computers are for) when possible.
Although I used the "Function" method I will try the others as well at some point too.

Thanks again for all the help.

Roger