Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    Star Lounger
    Join Date
    Mar 2003
    Location
    Ilkeston, Derbyshire
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •