By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,897 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

A simple query problem

P: n/a
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.

Dec 10 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On 10 Dec 2006 16:09:53 -0800, "Rex" <ra*******@gmail.comwrote:

One elegant solution is to write a public function in a standard
module:
public function GetMaxDate(byval 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(date1, 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.
Dec 11 '06 #2

P: n/a
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.
Dec 11 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.