IFF and Null Criteria Help

[kylem4711]

Hello All, Thank you for anyone that is willing to help.

Basically, i am trying to create a "complete" column but am having trouble. Can anyone notice any problems with this code?

Complete: IIf([Job Category]='Employee' And [L00642 Emp] Is Not Null,[L00642 Emp],IIf([Job Category]='Employee' And [L00641 Mgr] Is Not Null,[L00641 Mgr],IIf([Job Category]='Student' And [L00644 Std] Is Not Null,[L00644 Std],IIf([Job Category]='Student' And [L00642 Emp] Is Not Null,[L00642 Emp],IIf([Job Category]='Manager' And [L00641 Mgr] Is Not Null And [3L00643] Is Not Null And [L00641 Mgr]>[3L00643 WBT],[L00641 Mgr],[3L00643 WBT])))))

that looks a little weird, so i thought i would enter it here too.

Complete: IIf([Job Category]='Employee' And [L00642 Emp] Is Not Null,[L00642 Emp],IIf([Job Category]='Employee' And [L00641 Mgr] Is Not Null,[L00641 Mgr],IIf([Job Category]='Student' And [L00644 Std] Is Not Null,[L00644 Std],IIf([Job Category]='Student' And [L00642 Emp] Is Not Null,[L00642 Emp],IIf([Job Category]='Manager' And [L00641 Mgr] Is Not Null And [3L00643] Is Not Null And [L00641 Mgr]>[3L00643 WBT],[L00641 Mgr],[3L00643 WBT])))))

 

[Guus2005]

You should create a table which contains all Job Categories and then Join it.
It speeds up execution and is better to read!

HTH

 

[kylem4711]

one of the problems that i am having deals with the Employee, it shows that they have taken a class L00641 Mgr, but it does not show the completion date in the column.

it also does not list the later date in the completion date. I try to set it to max, but then it gives me an error?

so frustrating!

 

[Guus2005]

You have to create a separate query with all the MAX dates and then link it to the query you have. This is the best answer i can give you without a sample database.

HTH