how to choose the max between two dates or fields

[alhomam]

hi all,

i have a table that has the two fileds:

Return Date, Extension Time for Return Date

i need a query to choose the max date between these two fields

eg: if return date = 01 May 2008
ext return date = 10 may 2008


then the query should choose 10 may 2008

thanks for your help

[Tom van Stiphout]

Re: how to choose the max between two dates or fields

On Mon, 21 Apr 2008 23:47:40 -0700 (PDT), alhomam
<abbas2009@gmai l.comwrote:

You could use the IIf function (see help file), something like:
select iif(x>y, x, y) as MaxDate
from SomeTable

(x and y are your date fields)

-Tom.

>hi all,
>
>i have a table that has the two fileds:
>
>Return Date, Extension Time for Return Date
>
>i need a query to choose the max date between these two fields
>
>eg: if return date = 01 May 2008
ext return date = 10 may 2008
>
>
>then the query should choose 10 may 2008
>
>thanks for your help

[Benny Andersen]

Re: how to choose the max between two dates or fields

On Mon, 21 Apr 2008 23:47:40 -0700 (PDT), alhomam wrote:

hi all,
>
i have a table that has the two fileds:
>
Return Date, Extension Time for Return Date
>
i need a query to choose the max date between these two fields
>
eg: if return date = 01 May 2008
ext return date = 10 may 2008
>
>
then the query should choose 10 may 2008
>
thanks for your help

return date: f1
ext return date: f2

(f1+f2+abs(f1-f2))/2, possible showed using format(...
--
Benny Andersen

[lyle fairfield]

Re: how to choose the max between two dates or fields

On Apr 22, 2:47 am, alhomam <abbas2...@gmai l.comwrote:

hi all,
>
i have a table that has the two fileds:
>
Return Date, Extension Time for Return Date
>
i need a query to choose the max date between these two fields
>
eg: if return date       = 01 May 2008
        ext return date = 10 may 2008
>
then the query should choose 10 may 2008
>
thanks for your help

hack:

MaxDate = -(Date1 Date2) * Date1 - (Date2 Date1) * Date2

[Tom van Stiphout]

Re: how to choose the max between two dates or fields

On Tue, 22 Apr 2008 14:20:05 -0700 (PDT), lyle fairfield
<lyle.fairfield @gmail.comwrote :


LOL. This is why I always read your posts!
Especially writing:
-(Date1 Date2)
rather than
(Date1 < Date2)
is a gem.

-Tom.

>On Apr 22, 2:47 am, alhomam <abbas2...@gmai l.comwrote:

>hi all,
>>
>i have a table that has the two fileds:
>>
>Return Date, Extension Time for Return Date
>>
>i need a query to choose the max date between these two fields
>>
>eg: if return date       = 01 May 2008
>        ext return date = 10 may 2008
>>
>then the query should choose 10 may 2008
>>
>thanks for your help

>
>hack:
>
>MaxDate = -(Date1 Date2) * Date1 - (Date2 Date1) * Date2

[alhomam]

Re: how to choose the max between two dates or fields

On Apr 23, 6:54 am, Tom van Stiphout <no.spam.tom7.. .@cox.netwrote:

On Tue, 22 Apr 2008 14:20:05 -0700 (PDT), lyle fairfield
>
<lyle.fairfi... @gmail.comwrote :
>
LOL. This is why I always read your posts!
Especially writing:
-(Date1 Date2)
rather than
(Date1 < Date2)
is a gem.
>
-Tom.
>
>
>

On Apr 22, 2:47 am,alhomam<abba s2...@gmail.com wrote:

hi all,

>

i have a table that has the two fileds:

>

Return Date, Extension Time for Return Date

>

i need a query to choose the max date between these two fields

>

eg: if return date       = 01 May 2008
        ext return date = 10 may 2008

>

then the query should choose 10 may 2008

>

thanks for your help

>

hack:

>

MaxDate = -(Date1 Date2) * Date1 - (Date2 Date1) * Date2- Hide quoted text -

>
- Show quoted text -

hi all,

thank you all for trying to help me

actually i found the solution on microsoft website
they have designed on function called maximum and another one called
minimum
and that is what i was looking for

here is the link

http://support.microsoft.com/kb/209857

and here is the code

Function Minimum(ParamAr ray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Variant

' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)

' Cycle through each value from the row to find the smallest.
For I = 0 To UBound(FieldArr ay)
If FieldArray(I) < currentVal Then
currentVal = FieldArray(I)
End If
Next I

' Return the minimum value found.
Minimum = currentVal

End Function

Function Maximum(ParamAr ray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Variant

' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)

' Cycle through each value from the row to find the largest.

For I = 0 To UBound(FieldArr ay)
If FieldArray(I) currentVal Then
currentVal = FieldArray(I)
End If
Next I

' Return the maximum value found.
Maximum = currentVal

End Function


thanks all