Help with a calculated table field

[Bham45]

I'm using an Access template called "Charitable Contributions web database" (converted to desktop) that has a calculated field that takes a donor's first name and last name and creates a new field using first name and last name. So
First Name: John
Last Name: Doe
Results in Donor: John Doe.
This is the expression: IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]),IIf(IsNull([FirstName]),[LastName],[FirstName] & " " & [LastName]))

I want to be more formal and address John Doe as Mr. Doe. And if there's a Spouse named Jane, then Mr. and Mrs. Doe. So I added fields for SpouseFirstName and Title. Can anyone tell me how I would adjust the above expression to result in Mr. and Mrs. John Doe? By the way, I'm not sure why [Company] appears in the above expression; seems highly unlikely I would ever be addressing a company and not an individual.

Thanks!

 

[GinaWhipp]

Heh! I know you!

Okay, now that i've had my fun...

To compile an IIf() statement to take all the variations into account is not the way I would go. I would use fields for that, i.e. Mailing Name, where you could select which name to use; Status, where you could select Married, Single, etc... this will help determine which Salutation to use.

Side note: Yep, contributions are made in the name of Companies. So, the thanks letter would go to the Company (usually opened by the CEO or President) and posted for everyone to see. You may never receive contributions that way but it does happen.

 

[Bham45]

Didn't want to bother you if you were still sleeping... ;-)

I was just now trying to figure out how to simply combine Title, FirstName, and LastName in one field. Seems that it ought to be easier than the above expression. Is there a way?

Haha! Just got it: [Title] & " " & [FirstName] & " " & [LastName]

Things are starting to come back to me...

 

[GinaWhipp]

No bother, not sleeping... cleaning but I see you figured it out anyway!

 

[Bham45]

Okay, help me with another problem--please.

The Donations table in the template has a field called DonorID with these particulars:
Type = Number (That's my first question: why "number" when the result is text?)
Field Size: Long Integer
Lookup:
Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT [Donors].[ID], [Donors].[DonorName] FROM Donors ORDER BY [DonorName];
Bound Column: 1
Column Count: 2

All this results in showing the donor name as "John Doe" in the Donations Table--pulling the field "DonorName" from the Donors table. (DonorName being the calculated field mentioned in the first post.)

What I'm trying to do is replace this "DonorName" with my newly-created field "AddressAs"--but I can't get it to work in the same way. Any idea what I need to do?

Trying to do all this before proceeding with the mail merge issue in my other thread so I can get the names as I want them to appear on the letters.

Thanks again!

 

[GinaWhipp]

Hmm, okay, does that databse have any personal information in it? I would like to see it so I can see the tables and relationships and what its doing. Would probably also help with the other thread.

 

[Bham45]

I could make a copy and put some fake info in it. How can I share it with you?

Edit: Shoot! I did something bad and messed up that Donor table. Will re-import the original but it looks like I have to re-do a bunch of stuff I did last night...oy.

:banghead:

 

[vbaInet]

I was just now trying to figure out how to simply combine Title, FirstName, and LastName in one field. Seems that it ought to be easier than the above expression. Is there a way?

Haha! Just got it: [Title] & " " & [FirstName] & " " & [LastName]

Just going to add... as long as you ensure that all three fields must contain text during input.

 

[Bham45]

So that would be "cannot be blank" or whatever the phrase is?

Thanks!

 

[vbaInet]

Yes, all three must contain text otherwise you'll end with leading or trailing spaces or incomplete salutations. Just fyi.

 

[GinaWhipp]

Bham45

I'm going to fix that all or nothing issue with names, so don't worry about that. Once you fix whatever is *broken* just zip and you can upload here. Again, make sure no personal information is in the Tables.

 

[Bham45]

Okay, let's see if I can upload it here.

Here's what I'd like to be able to do right off:

In the Donors table, I want to replace "DonorName" with "FileAs". That means, instead of the many instances throughout the entire db of "DonorName" being shown as "John Smith", I want to instead use the "FileAs" version which is "Smith, John". I don't know if that means deleting DonorName and renaming FileAs as DonorName.

The second thing I'd like to do is replace instances of "Campaign" with "CampaignArea". See the table named Campaigns. I have two campaigns for Southern and Central Alabama 2014 (all info is fictional), but I want letters to say, "Thank you for your donation supporting the Mobile [or Montgomery] area."

It's in the Donations table that I would want to replace CampaignID with CampaignArea from the Campaign table. Don't know how to do that. (Again, I don't know why the designer of this db uses "number" as type for CampaignID when the result is text.)

Can you follow all this?

Here's the ultimate goal: I want to be able to send a letter to a donor saying

Dear Mr and Mrs Smith,

Thank you for the following donation(s) supporting our efforts in the [Montogmery or Mobile] area.

Donation Date Donation Amount Payment Method
Additional lines if more than one donation

Sincerely,

John Doe

I've got to go do some housework--have spent the last 3 days on this and everything else is falling apart. Our cat has turned our office into a litter box and I have to figure out what's up with that. The fumes are getting to me.

 

[GinaWhipp]

Okay, got database and opened. Have not really reviewed but I'll do over the course of the day and will answer/ask you/r questions as I go along.

Hmm, perhaps the cat is upset over the lack of attention?

 

[GinaWhipp]

Okay, so I started at the top and I see some changes that should be made but my first question is...

Is...
FileAs - Always LastName, FirstName?
AddressAs - Mr. & Mrs. Blah Blah?
DonorName - Always Husband FirstName LastName? What about the wife?

Why is TotaledDonated in the Donor table? That total can be gotten any time from Donations table.

 

[Bham45]

All but "AddressAs" were there in the original db. I have no idea either why TotalDonated is in the donor table--there are a bunch of things I come across and wonder why they're there but am afraid to delete them just yet. (I DID get rid of the User table, though.)

FilesAs: LastName, FirstName YES
AddressAs: Mr & Mrs, or Ms, or Dr, etc. YES
DonorName: I'm wondering if I can get rid of that field altogether and use FileAs since I would rather have everything sorted by last name. If it's a woman, then First Name will be her name. If they're married but the wife is making all the donations, then I would address her as Ms. Jane Doe. (Trivia for the day: Mrs. only goes with the husband's name-she's the "missus" of John Doe, but by herself she's Ms. Jane Doe. It's actually not a modern thing but has been done for centuries.)

Will be out most of today, but I appreciate your help whenever!

 

[GinaWhipp]

Okay, will try to get back to tonight... Sunday trying to take a day of rest!

Re: Mrs.

I have been saying that for YEARS! I have to tell people a woman cannot be married to herself so it's either... Mrs. John Doe OR Ms. Jane Doe but never Mrs. Jane Doe.

 

[Bham45]

How funny: you're the first person who hasn't given me an argument over it--much less totally agreeing. Great minds...

Talk to you tomorrow!

 

[GinaWhipp]

Okay, going to take these question a little at a time as this Template is a mess. (Not because of you, it *came* like this.)

Post# 3
Haha! Just got it: [Title] & " " & [FirstName] & " " & [LastName]

Reply to Post# 3: I cannot find where you are using this. I want to adjust so it is not dependant on all the fields being filled in. OR are you referring to the Calculated Field in the Donors table? (Side note: I hate Calculated Fields as there is no good reason to store this value taking up space when it can be calculated on the fly at any time.)

Post# 5
Type = Number (That's my first question: why "number" when the result is text?)
Field Size: Long Integer
Lookup:
Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT [Donors].[ID], [Donors].[DonorName] FROM Donors ORDER BY [DonorName];
Bound Column: 1
Column Count: 2

Reply to Post#5: You store the Foreign Key and linking to the Table that stores the Primary Key. What is stored in a field has nothing to do with how it's displayed. This is standard practice and should not be changed. So, as an example, my ID is 100 and my name is Gina Whipp. Now, I get married and my last name changes to Doe. By linking to my ID, 100, my last name changes all over the place to Gina Doe without having to go update any Tables or Queries because of that link. Understand?

I need to know which Forms you are using. Especially because I would like to see the set up of the ones you are going to be using for the Mail Merge.

 

[Bham45]

To your first question, yes, I added the "AddressAs" calculated field to the Donors table--only because I was trying to copy what the designer did with DonorName (and wondering if I could get rid of DonorName, using FileAs in most instances). "AddressAs" is only there because I didn't know where else I could do this calculation for a "Dear Mr. and Mrs. Doe" in a letter.

I'll go take a look at forms. I honestly don't understand why there are so many. All I would want--I think--is a form for adding a new donor and another form for entering donations. Tasks and Events are not an issue right now.

Not sure where I would select the donors to whom I want to send a thank-you letter. I assume that would be a query, not a form--but I haven't thought that far yet.

 

[GinaWhipp]

We'll get to the Donors Name issue after we we work out the other stuff first. Let's just get to the Forms you are using so I'm not looking at all these ones in this database.