how to choose the max between two dates or fields

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

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

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

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

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

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

On Apr 22, 2:47=A0am, 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 =A0 =A0 =A0 =3D 01 May 2008
> =A0 =A0 =A0 =A0 ext return date =3D 10 may 2008
>
> then the query should choose 10 may 2008
>
> thanks for your help

hack:

MaxDate =3D -(Date1 > Date2) * Date1 - (Date2 > Date1) * Date2

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

On Tue, 22 Apr 2008 14:20:05 -0700 (PDT), lyle fairfield
wrote:


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

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

On Apr 23, 6:54=A0am, Tom van Stiphout wrote:
> On Tue, 22 Apr 2008 14:20:05 -0700 (PDT), lyle fairfield
>
> wrote:
>
> 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=A0am,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 =A0 =A0 =A0 =3D 01 May 2008
> >> =A0 =A0 =A0 =A0 ext return date =3D 10 may 2008
>
> >> then the query should choose 10 may 2008
>
> >> thanks for your help
>
> >hack:
>
> >MaxDate =3D -(Date1 > Date2) * Date1 - (Date2 > Date1) * Date2- Hide quot=
ed 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(ParamArray 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 =3D FieldArray(0)

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

' Return the minimum value found.
Minimum =3D currentVal

End Function

Function Maximum(ParamArray 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 =3D FieldArray(0)

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

For I =3D 0 To UBound(FieldArray)
If FieldArray(I) > currentVal Then
currentVal =3D FieldArray(I)
End If
Next I

' Return the maximum value found.
Maximum =3D currentVal

End Function


thanks all