Convoluted query and list box question

[Tal]

I have a donor database with the following fields in a query:

LastName
FirstName
Spouse
Company

I am using reports to print receipts and acknowledgements, and forms to
intake donations etc.
Sometimes I need the receipt to go to (First Name & Last Name), sometimes
(Spouse & LastName) sometimes (First Name & Spouse & LastName) etc.
I have written the expressions in a query as separate fields for each
possible permutation.
How can I put a control on a form that would let the user select which
permutation to use.

Thanks,
Talia

 

[Danny Seager]

I would add a number field as a FK to a lookup table.

The lookup Table would be something like

TblNameLookup
NameLookupID (PK - Autonumber)
NameLookupDescription

Then add a combo box to your form, bind it to the lookup field in your main
table and have it's record source something like

SELECT * FROM TblNameLookup

Then you can use the Switch() function in a query to select which fields to
use

Switch([NameLookupID]=1,[FirstName] & [LastName],[NameLookupID]=2,[Spouse] &
[LastName],[NameLookupID]=3,[First Name] & [Spouse] & [LastName])

 

[Tal]

Thank you Danny.
I will give it a go and let you know how it turns out. I am afraid I have
gotten bogged down with a few other issues.
Many thanks,
Talia