Query to return latest date

[ms_access_00]

Hi

I have a table with a 200 project codes and four seperate date fields for each project. For example:

Project¦ Date1 ¦ Date2 ¦ Date3 ¦ Date4 ¦
F12345 10/01 24/02 04/05
19/07

Not every date will be populated. So some projects may only have the first two date fields populated.

How can I write a query to return the latest date for each project?

I have a field called LatestDate that I want to update with the date.

I tried writing an update query that would check to see if Date4 is null and then move to Date3 and so on but it does not work.

 

[jdraw]

Before you make any Updates, you should normalize your Table.
Fields like Date1, Date2, ...DateX usually indicate your table in not normalized.

Get your table properly structured and your queries etc will be much more straight forward.

Tell us more about your table - what does it represent? Why are your "dates" formatted as they are?

 

[pr2-eugin]

Hello and Welcome to AWF.. I hope that A project will have a maximum of 4 dates and the latest date will be in the Date4 Field. If that is correct.. try using the Nz().. something like..

SELECT [COLOR=RoyalBlue]RefTable1.CO_CODE[/COLOR], Nz([[COLOR=RoyalBlue]D4[/COLOR]],Nz([[COLOR=RoyalBlue]D3[/COLOR]],Nz([[COLOR=RoyalBlue]D2[/COLOR]],[[COLOR=RoyalBlue]D1[/COLOR]]))) AS BestDate
FROM [COLOR=RoyalBlue]RefTable1[/COLOR];

All blue bits need to change as per your requirement/design..

 

[ms_access_00]

Thanks for your reply pr2-eugin. That is great.

What is the best way to then update the table with this query so that the the Latest Date field is populated with it.