Finding the Later of 2 Date fields in a query to make a 3rd Date

Hi All

I have a query based on a single Table the has the
following fields:
Field 1: BoughtDate
Field 2: SoldDate

Sometimes the SoldDate is earlier than the BoughtDate

What I am trying to do is put the LATER one of
these 2 date fields in a 3 date field called:
LastTradeDate:

Any ideas?

Regards
Bob

Re: Finding the Later of 2 Date fields in a query to make a 3rd Date

I think you can just do a quick comparison in an IIF statement to
populate the third date field, like this:

SELECT Table1.BoughtDate, Table1.SoldDate, IIf([BoughtDate]>[SoldDate],
[BoughtDate],[SoldDate]) AS LastTradeDate
FROM Table1;

Here are my results:

BoughtDate SoldDate LastTradeDate
10/1/2007 11/1/2007 11/1/2007
12/1/2007 11/25/2007 12/1/2007
10/2/2007 10/3/2007 10/3/2007
10/1/2007 9/29/2007 10/1/2007
10/1/2007 8/28/2007 10/1/2007
8/31/2007 9/4/2007 9/4/2007

I hope that helps.

Re: Finding the Later of 2 Date fields in a query to make a 3rd Date field

Hi Bob,

I was trying an update with a self join which works in transact sql (for
sql server), but Jet sql (for Access) does not support updates on self
joined tables. Here is what the query looks like (that works -- using
Transact syntax in sql server)

UPDATE tblDates AS t1 INNER JOIN [Select * FROM
(Select RowID, date1 As dateR FROM tblDates WHERE date1 > date2
union all select RowID, date2 As dateR From tblDates where date2 >
date1) tA]. AS t2 ON t1.RowID=t2.RowID SET t1.date3 = t2.dateR;

The error I got was that Jet needs an updatable query. So the
alternative would be to use 2 separate query operations where you update
your 3rd date column first by checking if the boughtDate is greater than
the soldDate. Then run a 2nd update query where the soldDate is
greatedr than the BoughtDate.

Update yourtbl set date3 = dateb
where dateb > date2

Update yourtbl set date3 = dates
where dates > dateb

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Finding the Later of 2 Date fields in a query to make a 3rd Date field

I came up with a better solution

update tblDates set date3 = iif(date1 > date2, date1, date2)

Now you can do it in one shot

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Finding the Later of 2 Date fields in a query to make a 3rd Date

Thanks guys that really is a great help
I have learnt somthing new

Regards
Bob