Find the Latest Date 1

[Bill4tektips]

I have 3 date fields in a table and I need to find which one is the latest to form the basis for a report. The 3 Fields are [CorrPCD], [RemPCD] and [LatestPCD]. I have tried various combinations in a query but have so far failed. I need to improve my sql skills. My last attempt was:

LatestPCD: IIf([tblNonConformances]![RemPCD]>=[tblNonConformances]![LatestPCD], [tblNonConformances]![RemPCD] Or IIf([tblNonConformances]![CorrPCD]>=[tblNonConformances]![RemPCD],[tblNonConformances]![CorrPCD],[tblNonConformances]![LatestPCD]))

Can anyone help please?

 

[Spenney]

Something like...

LatestOf3Dates:IIf([RemPCD]>=[CorrPCD] And [RemPCD]>=[LatestPCD],[RemPCD],IIf([CorrPCD]>=[RemPCD] And [CorrPCD]>=[LatestPCD],[CorrPCD],IIf([LatestPCD]>=[RemPCD] And [LatestPCD]>=[CorrPCD],[LatestPCD],"")))

 

[Bill4tektips]

I am getting a lot of #Error and I think it is where some fields are blank. How do I prevent this from happening?

 

[PHV]

You may try this:
LatestOf3Dates: IIf(Nz([RemPCD])>=Nz([CorrPCD]) And Nz([RemPCD])>=Nz([LatestPCD]),[RemPCD],IIf(Nz([CorrPCD])>=Nz([RemPCD]) And Nz([CorrPCD])>=Nz([LatestPCD]),[CorrPCD],[LatestPCD]))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Bill4tektips]

PHV You're a genius. Works perfect. Many thanks