IIF Statement

  • Thread starter Thread starter echofuzz
  • Start date Start date
E

echofuzz

Hi, all.

I'm having issues with an IIF statement in an audit database report. I have
a [TargetDate] and a [CompletionDate]. I need to be able to return "Overdue"
if the there is no [CompletionDate] and [TargetDate] has already passed OR if
the [TargetDate] < [CompletionDate].

The expression I came up with is:

=IIf(Eval([TargetDate]<[CompletionDate]) Or (([CompletionDate] Is Null) And
([TargetDate]<Date())),"Overdue","OK")

I get an #Error if no [CompletionDate] is entered, so I think that's the
issue. I'm not sure how to rectify it. Any suggestions?

Let me add that I'm not a programmer, but I sometimes have to develop simple
databases for work.

Thanks in advance!
 
Try this --
=IIf(([CompletionDate] Is Null And [TargetDate]<Date()) OR
([TargetDate]<[CompletionDate]),"Overdue","OK")
 
IIF((TargetDate<Date() AND CompletionDate is Null) OR
TargetDate<CompletionDate, "Overdue",Null)

OR you can try this variation

IIF(TargetDate<Nz(CompletionDate,Date()),"Overdue","")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top