# Thread: Formula (Access 2000 and XP)

1. ## Formula (Access 2000 and XP)

My database contains the fields:

[MaritalStatus] where the input can be Single, Never Married, Divorced, Widow, Widower.

In the next (calculated) field, we want a return based upon:

if the person is a Widow, return a blank; otherwise return the Wife

2. ## Re: Formula (Access 2000 and XP)

You are putting Or's between several IIf's. Access sees this as a logical expression. But I don't understand what you are doing here, I think because there seems to be no marital status "Married".

3. ## Re: Formula (Access 2000 and XP)

The following is what I should have sent, which includes married:

=IIf([MaritalStatus]="Widow"," ",[Wife's Maiden Name]) Or IIf([MaritalStatus]="Married",[KINFIRST] & " " & [KINLAST]) Or IIf([MaritalStatus]="Never Married",[KINFIRST] & " " & [KINLAST]) Or IIf([MaritalStatus]="Divorced",[KINFIRST] & " " & [KINLAST]) Or IIf([MaritalStatus]="Single",[KINFIRST] & " " & [KINLAST])

The end result needs to be:

If someone is Married, the return should be the Next of Kin name
If someone is Widower, the return should be the Wife's Maiden Name
If someone is Single, Never Married, or Divorced, the return should be the Next of Kin name

This is a database that I developed years back for a funeral service and we are trying to be able to print death certificates, have spaced it out so that it prints right on the form.

Does the above explain it a little better?

Thanks.

4. ## Re: Formula (Access 2000 and XP)

Does this do what you want?

=IIf([MaritalStatus]="Widow";Null;IIf([MaritalStatus]="Widower";[Wife's Maiden Name];[KINFIRST] & " " & [KINLAST]))

It says: if the marital status is "Widow", return a blank. Otherwise, if the marital status is "Widower", return the Wife's Maiden Name, in all other cases, return the Next of Kin. We don't need to specify all the other cases explicitly.

5. ## Re: Formula (Access 2000 and XP)

That would have done it - and I thank you - but the rules have changed. This is what has to happen:

Here

6. ## Re: Formula (Access 2000 and XP)

The brackets aren't placed correctly in your formula. Moreover, what should be returned if none of these conditions is met?

7. ## Re: Formula (Access 2000 and XP)

Try this:
IIf([MaritalStatus]="Widowed","",IIf([Sex]="Male" And [MaritalStatus]="Married",[Wife's Maiden Name],IIf([Sex]="Female" And [MaritalStatus]="Married",[KINFIRST] & " " & [KINLAST],"Not Applicable")))

You could change the "Not applicable" bit to something more appropriate.

HTH

Mark

8. ## Re: Formula (Access 2000 and XP)

I've just come back to this after having had knee surgery and being out of commission a few days.

Thank you, this did exactly what I needed.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•