Use a Proc to pass values to field in a Report?

[Sandra]

I may be asking for too much here, but I have a mailing
database with a master table for the Main list with PK of
PPID, and a child table of individuals related to the
Main, a one to many relationship. Each individual in the
child table has a unique PK of ANID, a PPID link, and a
identifier for Husband, Wife, Child; or Contact (if the
Main is a business).

My problem is this: I need a way to format the addresses
for mailers based on several conditions, and trying to do
it with if statements is driving me crazy. In order to get
down to just the 2 individuals I want for the mailer, I
have a query to obtain the records based on Husband, and a
query to obtain the records based on Wife. Now I have
created a report based on the Main table and the 2
queries. So far-so good. But now comes the hard part:
1. If there is no wife for the Main record, then the
address line needs to be Mr. John Doe (hisPrefix &
hisFName & hisLname) from the husband record).
2. If there is no husband, then the address line needs to
be Mrs./Miss Jane Doe (herPrefix & herFName & herLName)
from the Wife record).
3. If there is a husband and a wife then the address line
needs to be Mr. and Mrs. John Doe (hisPrefix & "and" &
herPrefix & hisFName & hisLName).
4. If HE is a doctor, then we have no problem with the
above conditions. If SHE is a doctor then the address
line needs to be Mr. John Doe and Dr. Jane Doe.

I'm thinking that if a proc can be written to address the
conditions using CASE, then a string of the required
fields could be passed to a field in the Report.

Or maybe there is a way to accomplish all this with a
Query?

Or maybe I should just put a salutation field in the Main
table and be content with redundant data LOL!

Any thoughts would be greatly appreciated...

TIA,
Sandra

 

[HSalim]

Try to avoid using functions in your query
Sorry, but there is not much choice in this matter.
Select Case may not be appropriate in this case - at any given time you have
only two choices

You can avoid a lot of this by not taking the short form.
The long form - writing out both names fully is perfectly acceptable, both
here and in the UK
as far as I know.
If you were to write
Mr John Doe and Mrs.Jane Doe
you can concatenate the Husband's 3 name fields into one, and similarly Wife

This still leaves us with the problem of the And, which is easily fixed
if Len(Husband) > 0 and Len(Wife) > 0 then
Husband & " And " & Wife
Else
Husband & Wife ' if either of them is null, or empty string, there is no
effect on the result
End if

HS

 

[Sandra]

Thanks for the tips!
Sandra

-----Original Message-----
Try to avoid using functions in your query
Sorry, but there is not much choice in this matter.
Select Case may not be appropriate in this case - at any given time you have
only two choices

You can avoid a lot of this by not taking the short form.
The long form - writing out both names fully is perfectly acceptable, both
here and in the UK
as far as I know.
If you were to write
Mr John Doe and Mrs.Jane Doe
you can concatenate the Husband's 3 name fields into one, and similarly Wife

This still leaves us with the problem of the And, which is easily fixed
if Len(Husband) > 0 and Len(Wife) > 0 then
Husband & " And " & Wife
Else
Husband & Wife ' if either of them is null, or empty string, there is no
effect on the result
End if

HS





.