Can Dmin and Dmax find the min/max dates in a date field? Docs mention numbers and strings, but not dates and my code is not giving the correct results.

Example: mydate=Dmin("[startdate]","tmpQry","")

Code:


dtstart returns Null, and dtend returns 12:00am. This is not what the query reflects.

Is there a function that will find the lowest date/time and act like Dmin? How about a similar function for Dmax and dates?

I have used DMin() and DMax() for dates on many occassions. In your example: mydate=Dmin("[startdate]","tmpQry",""), have you tried:

mydate=Dmin("[startdate]","tmpQry")

In regards to your code, I would probably check to make sure at least 1 record is returned using the criteria. Additionally you might check the values of dt and crit with a debug.print statement to make sure you are getting what you expect. Out of curiosity is tmpTimesheet.COSTDATE part of the tmpCalcDailySumm recordset?

>>Dim dtstart, dtend as date<<

Be careful with Dim statement in VBA. Your Dim statement above declares dtstart as Variant, not Date. In VBA, you need to declare the data-type for each variable, even on the same Dim statement.


>>crit = "(((tmpTimesheet.COSTDATE)=#" & dt & "#) and (PCodes.Payable=True))"<<

I suspect the Table Qualiers are the problem here. From what you posted, "tmpCalcDailySumm" is a Query being used as the Domain for your DMin / DMax expressions. In this Domain, the "source" Tables for the Query have "disappeared" and therefore, the use of the Table qualifiers are not correct when the criteria are being applied to the Domain.

Try the criteria construction without the qualifiers [tmpTimesheet] and [PCodes]...

First, dim the variables correctly. DMin and DMax may return Null.
Second, dates in a criterium must be formatted as date expression strings.
Thus:

/gustav