Problem with IIf statement

[Linkerbink]

I have an employee database which includes the
fields "HireDate", "TermDate", "RehireDate"
and "RetermDate". These fields are used repectively when
an employee is hired, leaves, is re-hired, and leaves
again after the re-hire. I want to put a text box on the
employee's profile form which returns the word "Active" if
the TermDate is null (showing the person has never quit),
OR if TermDate and RehireDate both are not null and
RetermDate is null (showing that the person quit, was re-
hired, and has not quit a second time. All other results
would return the word "Inactive". I know there's a way to
do it by nesting IIf statements, but I can't seem to get
it right. Any help out there?

 

[Les]

Try this:

IIf(IsNull([Termdate]),"active",IIf(Not IsNull([Termdate])
And Not IsNull([rehiredate]) And IsNull
([retermdate]),"Active","inactive"))

 

[Guest]

Thanks...it worked! You guys came through again!

-----Original Message-----
Try this:

IIf(IsNull([Termdate]),"active",IIf(Not IsNull ([Termdate])
And Not IsNull([rehiredate]) And IsNull
([retermdate]),"Active","inactive"))

-----Original Message-----
I have an employee database which includes the
fields "HireDate", "TermDate", "RehireDate"
and "RetermDate". These fields are used repectively when
an employee is hired, leaves, is re-hired, and leaves
again after the re-hire. I want to put a text box on the
employee's profile form which returns the word "Active" if
the TermDate is null (showing the person has never quit),
OR if TermDate and RehireDate both are not null and
RetermDate is null (showing that the person quit, was re-
hired, and has not quit a second time. All other results
would return the word "Inactive". I know there's a way to
do it by nesting IIf statements, but I can't seem to get
it right. Any help out there?
.

.