MS Access Query - Max of Three Date Field and Null Values

StartDate: IIF(NZ([Estimated_Start_Date],0)>=NZ([Revised_Start_Date],0),IIF(NZ([Estimated_Start_Date],0)>=NZ([Estimated_Comp_Date],0), NZ([Estimated_Start_Date], 0, NZ([Estimated_Comp_Date], 0)

Steve

Getting a bracketing error trying to locate it but if you find it sooner great

I don't know if this is what you want but here goes:
(see picture attached)

select
  case
    when Estimated_Start_Date >= Revised_Start_Date and Estimated_Start_Date >= Estimated_Comp_Date then Estimated_Start_Date
    when Revised_Start_Date >= Estimated_Start_Date and Revised_Start_Date >= Estimated_Comp_Date then Revised_Start_Date
    else Estimated_Comp_Date
  end as START,
  Estimated_Start_Date,
  Revised_Start_Date,
  Estimated_Comp_Date
from DATES

Select allOpen in new window

start-date.jpg

you could create a function (see attached) in a standard module
Simply NZ([EachFieldName])  in your query when calling the function

Try the following in the immediate window
?LargestOf3Dates NZ(Date()),NZ(Date()+2),NZ(Date()-2))

or

?LargestOf3Dates NZ(Date()),NZ(Null),NZ(Date()-2))

in your case you would have a field in your query

MaxDate:?LargestOf3Dates NZ([Estimated_Start_Date]),NZ([Revised_Start_Date]),NZ([Estimated_Comp_Date]))

Option Compare Database
 
Public Function LargestOf3Dates(Optional dat1 As Date, Optional dat2 As Date, Optional dat3 As Date) As Date
Dim datMaxDate As Date
 
 
    If IsDate(dat1) Then
        datMaxDate = dat1
    End If
    
    If IsDate(dat2) Then
        If IsNull(datMaxDate) Then
            datMaxDate = dat2
        Else
            If dat2 > datMaxDate Then
                datMaxDate = dat2
            End If
        End If
    End If
    
    If IsDate(dat3) Then
        If IsNull(datMaxDate) Then
            datMaxDate = dat3
        Else
            If dat3 > datMaxDate Then
                datMaxDate = dat3
            End If
        End If
    End If
    
    If IsNull(datMaxDate) Then
        'All three dates were null
        'Determine what you want to return
    Else
        LargestOf3Dates = datMaxDate
    End If
    
End Function

Select allOpen in new window

Id rather steer clear of code on this one if possible.  

No code here.... Plain and simple SQL.

select
  case
    when Estimated_Start_Date >= Revised_Start_Date and Estimated_Start_Date >= Estimated_Comp_Date then Estimated_Start_Date
    when Revised_Start_Date >= Estimated_Start_Date and Revised_Start_Date >= Estimated_Comp_Date then Revised_Start_Date
    else Estimated_Comp_Date
  end as START,
  Estimated_Start_Date,
  Revised_Start_Date,
  Estimated_Comp_Date
from DATES  -- Change to your table name

Select allOpen in new window

Ok I did this in two expressions for now:

EstStartDate: IIf(NZ([Revised_Start_Date],0),[Revised_Start_Date],[Estimated_Start_Date])
StartDate: IIf(NZ([Actual_Start_Date],0),[Actual_Start_Date],[EstStartDate])

The bottom one is the one I would be relying on to give me the most current start date.  

The above can only work because the dates are entered in sequence of more current (well they are supposed to be anyway)  So the estimated date would be entered then revised then last would be the actual start date.  Make sense?

Paul the sql is giving me a syntax error on the when part.

ty