Using IIf function for Query

[chriscardwell06]

I have a query driving a report that fills in the on time or late based on comparing the release date to the production due date. The code works except for if we haven't released it yet and the release date is void. I need it to then check versus today's date and then populate the late/on time. But I need this to take a back seat to if both fields are populated. Here is the code I am currently using and I have attached a screen shot of the query with the 2 boxes highlighted in red.

On Time / Late Eng: IIf([Released to Prod]<=[Prod Due Date],"On Time",IIf([Released to Prod]>[Prod Due Date],"Late",""))

 

[pr2-eugin]

Lookup on Nz() function..

 

[chriscardwell06]

I looked that function up and gave an attempt at it but it still didn't populate those fields. The expression I used is as follows.

On Time / Late Eng: IIf([Released to Prod]<=[Prod Due Date],"On Time",IIf([Released to Prod]>[Prod Due Date],"Late",IIf(Nz( [Released to Prod] ) and [Prod Due Date] < date(),"Late","On Time")

 

[pr2-eugin]

Is it possible to give an example set, of what data when present will have what outcome?

 

[chriscardwell06]

I have 2 dates. A released date and a due date. The first half compares them and sets the on time to either on time or late. That part works good. But we have some jobs that have a due date but we haven't released yet. The released is a blank value and I want it to look to say if released date is null and due date < today's date then late else on time. Does that make sense? There is a screen shot of the query in my first post.

 

[pr2-eugin]

So..

IIF(IsNull(releaseDate), IIF(dueDate <= Date(), "Late", "On Time"), IIF(releaseDate <= produceDate, "On Time", Late"))

 

[chriscardwell06]

Got it! Thanks!

 

[pr2-eugin]

No problem.