Creating a Summary Field

[Access::Student]

Hi,
I'm trying to create a calculated text box in one of my forms, on the table
"submissions". This is a status field that can be "preemptive" , "open" or
"closed". The way I want it to work has to do with a related table
"directives". Each directive has a "kf_submissions_if" and a "status" which
can be 'open' or 'closed'. Status in submissions is determined this way:
1. No related directive : Preemptive
2. Some related directives are open, some closed: Open
3. All related directives are closed : Closed

The expression i have so far is the following:

=IIf(IsNull(DLookUp("[status]","DIRECTIVES","kf_submissions_id=" &
[kp_submissions_id])),"Preemptive","Open")

What I need to do now is nest another IIf where "open" is to distinguish
between open and closed, but I don't know what function to use. It should
search all returned fields and match on "open". Any suggestions? thank for
the help.

 

[Arvin Meyer MVP]

I suggest using a Case Statement instead of the Immediate If.