Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Find the Latest Date 1

Status
Not open for further replies.

Bill4tektips

Technical User
Joined
Aug 5, 2005
Messages
175
Location
GB
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?
 
Something like...
Code:
LatestOf3Dates:IIf([RemPCD]>=[CorrPCD] And [RemPCD]>=[LatestPCD],[RemPCD],IIf([CorrPCD]>=[RemPCD] And [CorrPCD]>=[LatestPCD],[CorrPCD],IIf([LatestPCD]>=[RemPCD] And [LatestPCD]>=[CorrPCD],[LatestPCD],"")))
 
I am getting a lot of #Error and I think it is where some fields are blank. How do I prevent this from happening?
 
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
 
PHV You're a genius. Works perfect. Many thanks
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top