expression in query (1 Viewer)

rainbows

Registered User.
Local time
Today, 03:11
Joined
Apr 21, 2017
Messages
425
hi ,

this is what I use for my ontime results

problem with this is that I really don't get a true picture of what really happened say 6 years ago as at the date we did not have a dateclosed
what I would like to do ( but cannot do ) is say if the dateclosed was before 01/01/2018 put " unknown"

On_Time: IIf(IsNull([targetDate]),"",IIf([Dateclosed]>[targetDate] Or (([targetDate]<Date() And IsNull([Dateclosed]))),"Late","On Time"))

then

late: IIf([On_Time]="late",1,0)


how can the above formula be modified to say don't count the dateclosed records

thanks for you help

steve
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:11
Joined
May 7, 2009
Messages
19,175
On_Time: IIf(IsNull([targetDate]),"",iif([DateClosed]<#1/1/2018#, "Unknown", IIf([Dateclosed]>[targetDate] Or (([targetDate]<Date() And IsNull([Dateclosed]))),"Late","On Time")))
 

rainbows

Registered User.
Local time
Today, 03:11
Joined
Apr 21, 2017
Messages
425
thank you

that works great


steve
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:11
Joined
May 7, 2009
Messages
19,175
cheers mate!
 

Users who are viewing this thread

Top Bottom