Max and Min Determination for Dates

[McArthurGDM]

Hi,

I'd like to take two date values within a query and determine the max and/or min of each, within a new field.

I am not sure how to do this.

I have tried DMin and DMax functions, but they give me "#ERROR" as a response in my query.



Does anyone know a useful method to determine min and max for dates?

Thanks

[JoeM]

Please post your DMin, DMax formula attempts that are giving your errors.

If you describe your data layout, we can probably help you come up with what you need.

[McArthurGDM]

Here is an example of what it is I am doing and the Error that I am getting. There is no relationship established between the tables, but that's fine for the purposes of what it is I am trying to accomplish.

[McArthurGDM]

I realize that I am probably not using the DMin and DMax function correctly.

What I need to do either way is determine the Max or the Min as such:

MAX(Info.StartDate, Months.StartDate) and MIN(Info.EndDate, Months.EndDate)


Is there a good way to do this?

[JoeM]

Your functions aren't structured properly. See here: http://www.techonthenet.com/access/f...omain/dmax.php

Note that the second argument is just your object (table/query) name, no field attached to it.

[McArthurGDM]

I'm not convinced that the way the DMax and DMin as well as Max and Min functions work will supply the result I need.

I am looking for a simple mathematical Min(x,y) or Max(x,y) determination between two field values. I am not trying to find a particular Max or Min of a field as part of an aggregate function.

SOLUTION:

I managed a work-around.

Max(x,y) = IIF( x > y, x, y)

You need to convert from Date to Integer using the function CLng. Then you need to convert back to Date. The final output is as such...

Format(IIf(CLng([Info]![Start_Date])>CLng([Months]![Start_Date]),CLng([Info]![Start_Date]),CLng([Months]![Start_Date])),"m/dd/yyyy")

[amrut]

I think you need to use IIF(Date1>Date2,Date1,Date2) in your query instead of DMax. As pointed out, DMax is to find the MAX value from a table/query based on a criteria.
Edit : I left the window open for some time and when I replied, you already had the answer I was suggesting.

[JoeM]

I am looking for a simple mathematical Min(x,y) or Max(x,y) determination between two field values. I am not trying to find a particular Max or Min of a field as part of an aggregate function.

Well, that certainly is much different from your original question.
Glad you figured it out.