Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Find earliest of 3 dates in SQL

Status
Not open for further replies.

ZABADAK

Programmer
Feb 24, 2005
39
US
I'm reading a table that has 3 dates, closed, promoted and cancelled. Any or all of these dates can be populated or null. I need to find the earliest date or null if there are none and use this result in comparisons with other dates. Can this be done in SQL or should I create a query to fill an extra column with this date?
 
'A generic function to get the min value of an arbirtrary numbers of same type values:
Public Function myMin(ParamArray Args())
Dim i As Long, rv
rv = Args(LBound(Args))
For i = 1 + LBound(Args) To UBound(Args)
If IsNull(rv) Or rv > Args(i) Then rv = Args(i)
Next
myMin = rv
End Function

SELECT myMin([closed], [promoted], [cancelled]) As EarliestDate, ....

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
I tested this function and it does not work! If I input 1/1/05, 12/31/04 and null, it returns 1/1/05 which is not the earliest date.
 
Function also crashes on an invalid date which is not good.
 
I tested this function and it does not work!
Please don't shout - the answers won't come any faster.

Did you express 1/1/05 as a date by using # delimiters? If not, Access will have read the parameters as strings and "1/" does indeed come before "12" or "Nu"

Geoff Franklin
 
Where was I shouting?

I used the provided function exactly as described by the poster and it did not work.
I solved the problem myself yesterday. I was attempting to inform anyone else who might come across this 'answer' that it does not work.
 
ZABADADAK, in the debug window:
[tt]? myMin(#1/1/05#, #12/31/04#, Null)
31/12/2004[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
I solved the problem myself yesterday. I was attempting to inform anyone else who might come across this 'answer' that it does not work.

In that case you should have also provided your working solution so that the next person who came along would have some useful information instead of 'It doesn't work'.

Leslie
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top