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

[bobdydd]

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

[Filio]

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

SELECT Table1.BoughtDa te, 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.

[Rich P]

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.Row ID 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 ***

[Rich P]

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 ***

[bobdydd]

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

Regards
Bob