Is there a faster way to do this?

[Bullsandbears123]

Is there a faster way to do this?


dim getlastdate as date

getlastdate = nz(DMax(&quot;[appdate]&quot;, &quot;qryDate_qryExpPosUndHistory&quot;, &quot;[appdate]<=#&quot; & gblparadate & &quot;#&quot, &quot;&quot

This command takes a really long time to process. THANKS

 

[SteveR77]

Try changing the query into a table.

The issue is the query has to run then the DMax function has to be applied to it, and finally the not zero function (Nz) has to be executed.

A table would merely have the data available in it and because no processing would need to be done to generate the recordset that you would need.


I hope this helps,


Steve

 

[oharab]

I would use a recordset based on a query.

Dim rs as DAO.Recordset
set rs=currentdb.OpenRecordset(
&quot;SELECT MAX(qryDate_qryExpPosUndHistory.AppDate) AS MaxDate
FROM qryDate_qryExpPosUndHistory
WHERE appdate<=#&quot; & format(gblparadate,&quot;mm/dd/yy&quot & &quot;#&quot

getlastdate =rs!MaxDate

rs.Close
set rs=nothing

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

&quot;Where are all the stupid people from...
...And how'd they get so dumb?&quot;

NoFX-The Decline
----------------------------------------------

 

[Bullsandbears123]

Doesn't dmax do the samething, so I'd think it would take the same amount of time?

 

[SteveR77]

DMAX does do the same thing. It is the time that the query takes to provide you with a result that is the real issue.

In the case of the recordset object and a table these are &quot;static&quot; so whatever calculations you wish to apply to them can be done at that moment in time.

In the case of a query it has to execute to return the data set that you will need.

 

[oharab]

I've always assumed that running a query is quicker. I actually never use dmax/dlookup I use the functions from

http://www.mvps.org/access/modules/mdl0012.htm

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

&quot;Where are all the stupid people from...
...And how'd they get so dumb?&quot;

NoFX-The Decline
----------------------------------------------

 

[Bullsandbears123]

THanks