Max expression

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone explain how I should use a max function to calculate the most
recent Date from 6 date fields in a query. In excel I simply have the date
fields in columns A, B, C, D, E & F then use a max function/formula in G to
caluculate the most recent date.
 
Unfortunately, Access isn't a spreadsheet, like Excel is. Access likes to
have infomation, of similar data, to be stored in columns. So, you have
multiple columns with the info, but Access would expect this data to be in
only one column.

That way, you could issue a Max(DateField), and it would return the highest
value.
 
I can think of 2 ways without using the max, one would be an iif statement in
a new query field as follows

maxdate: IIf([date1]>[date2],[date1],[date2])

you could nest this ( see adjacent post for syntax ) for more dates )

you could also write a vb function maxdate ( date1,date2,date3...) that
would return the largest date. you would call the function in a field of the
query
 
I agree with Steve that this is easiest to do when the data is fully
normallized, but occassionally, I run into a situation where I want to find
the maximum value between several fields, or , more often, I want to get the
maximum from among two or more numbers in a code segment. I developed the
following function to achieve that task. I can pass it as many parameters
(of any type) as I want, and it will compare them and return the maximum
value. You can modify this slightly to get a minimum value as well.


Public Function Maximum(ParamArray MyArray()) As Variant

Dim lngLoop As Long
Maximum = Null

For lngLoop = LBound(MyArray) To UBound(MyArray)

If IsNull(MyArray(lngLoop)) Then
' don't do anything
ElseIf IsNull(Maximum) Or MyArray(lngLoop) > Maximum Then
Maximum = MyArray(lngLoop)
End If
Next

End Function
 
Thanks, just what I needed!

Mike said:
I can think of 2 ways without using the max, one would be an iif statement in
a new query field as follows

maxdate: IIf([date1]>[date2],[date1],[date2])

you could nest this ( see adjacent post for syntax ) for more dates )

you could also write a vb function maxdate ( date1,date2,date3...) that
would return the largest date. you would call the function in a field of the
query




Dave said:
Can anyone explain how I should use a max function to calculate the most
recent Date from 6 date fields in a query. In excel I simply have the date
fields in columns A, B, C, D, E & F then use a max function/formula in G to
caluculate the most recent date.
Click to expand...
Click to expand...
 
[MVP] S.Clark said:
Access likes to
have infomation, of similar data, to be stored in columns. So, you have
multiple columns with the info, but Access would expect this data to be in
only one column.

That way, you could issue a Max(DateField), and it would return the highest
value.
Click to expand...

But it could be kludged like this:

SELECT MAX(DT1.col_max) AS max_max
FROM (
SELECT MAX(A) AS col_max FROM MyTable
UNION
SELECT MAX(B) AS col_max FROM MyTable
UNION
SELECT MAX(C) AS col_max FROM MyTable
UNION
SELECT MAX(D) AS col_max FROM MyTable
UNION
SELECT MAX(E) AS col_max FROM MyTable
UNION
SELECT MAX(F) AS col_max FROM MyTable
) AS DT1
;

Jamie.

--
 
Back
Top