Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Replacing DMin statement 1

Status
Not open for further replies.

Mute101

Programmer
Jun 28, 2001
428
GB
Hi,

I'm not sure this is the right forum for this but here goes;

I have some legacy code for displaying dates based on the records in a query.

Code:
For vWeekday = 2 To 6
    '########### label all dates ####################
    vDespatch = Null
    strCriteria = "WeekDay(IdealDespatchDate) = '" & vWeekday & "'"
    vDespatch = DMin("[IdealDespatchDate]", "Q_CDProd", strCriteria)

The DMin function is VERY slow as I would expect but what would be the fastest method to retrieve the lowest date from a bunch of records?

Any help appreciated
Sim

----------------------------------------
It is of interest to note that while some dolphins are reported to have learned English -- up to fifty words used in correct context -- no human being has been reported to have learned dolphinese.
----------------------------------------
 
My method would be to use the .execute method of the ADO connection object, here using currentproject (native/linked tables):

[tt]dim rs as adodb.recordset
dim strSql as string
strSql="select min(IdealDespatchDate) from Q_CDProd " & _
"where " & strCriteria
set rs = currentproject.connection.execute(strSql)
if (not rs.bof and not rs.eof) then
debug.print rs.fields(0)
end if
rs.close
set rs=nothing[/tt]

Roy-Vidar
 
Perfect,

thanks Roy-Vidar once again for being spot on. As the database is quite old and I work with a bunch of has-beens its all written in DAO but you've pushed me in the right direction.

Thanks again for the prompt reply
Simon

----------------------------------------
It is of interest to note that while some dolphins are reported to have learned English -- up to fifty words used in correct context -- no human being has been reported to have learned dolphinese.
----------------------------------------
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top