A simple query problem

[Rex]

Hi,

I have a table in which for a particular record there are different
dates in each column. I want to find out the latest date of all.. so
for example

ID | date1 | date2 | date3 | date4 |
-----------------------------------------------------------------------
1 | 1/12/06 | 5/12/06 | 10/12/06 | 4/12/06 |

I want to create a query that would show:

ID | date1 | date2 | date3 | date4 | latestDate
---------------------------------------------------------------------------------------
1 | 1/12/06 | 5/12/06 | 10/12/06 | 4/12/06 | 10/12/06

any help would be greatly appreciated

Rex.

[Tom van Stiphout]

Re: A simple query problem

On 10 Dec 2006 16:09:53 -0800, "Rex" <rakeshv01@gmai l.comwrote:

One elegant solution is to write a public function in a standard
module:
public function GetMaxDate(byva l d1 as Date, d2 as Date, d3 as date,
d4 as date) as Date
dim d as date
d=d1
if d2>d then d=d2
if d3>d then d=d3
if d4>d then d=d4
GetMaxDate=d
end function
and then call it from your query:
select *, GetMaxDate(date 1, date23, date3, date4)
from SomeTable

If you must, you can probably do it with nested iif function calls as
well. See help file for iif (that was not a typo).

Another elegant one is to figure out if your database design is
normalized. Typically one could write:
select Max(SomeDate) from SomeTable where ForeignKey = xxx

-Tom.

>Hi,
>
>I have a table in which for a particular record there are different
>dates in each column. I want to find out the latest date of all.. so
>for example
>
ID | date1 | date2 | date3 | date4 |
>-----------------------------------------------------------------------
>1 | 1/12/06 | 5/12/06 | 10/12/06 | 4/12/06 |
>
>I want to create a query that would show:
>
>ID | date1 | date2 | date3 | date4 | latestDate
>---------------------------------------------------------------------------------------
>1 | 1/12/06 | 5/12/06 | 10/12/06 | 4/12/06 | 10/12/06
>
>any help would be greatly appreciated
>
>Rex.

[m.wanstall]

Re: A simple query problem

Create a function in VB, either using IF statements or a SELECT CASE
statement.

The function would simply be something like this if you used IFs:

Public Function findDate(date1 Aas Date, date2 As Date, date3 As Date,
date4 As Date)
If date1>date2 and date1>date3 and date1>date4 Then
findDate = date1
ElseIf date2>date1 and date2>date3 and date2>date4 Then
findDate = date2
ElseIf
'...put in date 3 here
Else
findDate = date4
End If
End Function

-Mal

Rex wrote:

Hi,
>
I have a table in which for a particular record there are different
dates in each column. I want to find out the latest date of all.. so
for example
>
ID | date1 | date2 | date3 | date4 |
-----------------------------------------------------------------------
1 | 1/12/06 | 5/12/06 | 10/12/06 | 4/12/06 |
>
I want to create a query that would show:
>
ID | date1 | date2 | date3 | date4 | latestDate
---------------------------------------------------------------------------------------
1 | 1/12/06 | 5/12/06 | 10/12/06 | 4/12/06 | 10/12/06
>
any help would be greatly appreciated
>
Rex.