need replacement for DLookup in UPDATE query

[bobabooey2k]

I have an update query with one field having in its "Update to" cell a
DLookup statement. This query takes 2-3 minutes on 3000 records.

Can I avoid dlookup here using multiple queries?

An underlying subquery to this Update query involves a MAX function on
a date field, which is then used in the DLookup statement.

Any help appreciated. Thanks
Richard

[DFS]

bo*********@yah oo.ca wrote:

I have an update query with one field having in its "Update to" cell a
DLookup statement. This query takes 2-3 minutes on 3000 records.

Can I avoid dlookup here using multiple queries?

An underlying subquery to this Update query involves a MAX function on
a date field, which is then used in the DLookup statement.

Any help appreciated. Thanks
Richard

Can you post your update query?

[bobabooey2k]

DFS wrote:

bo*********@yah oo.ca wrote:

I have an update query with one field having in its "Update to" cell a
DLookup statement. This query takes 2-3 minutes on 3000 records.

Can I avoid dlookup here using multiple queries?

An underlying subquery to this Update query involves a MAX function on
a date field, which is then used in the DLookup statement.

Any help appreciated. Thanks
Richard

Can you post your update query?

UPDATE [Client ACE Query] SET [Client ACE Query].[Archive Box Number] =
DLookUp("[Archive Box Number]","[Max ACE Box Number Query]","[Client
Number] ='" & [Client ACE Query]![Client Number] & "'")
WHERE ((([Client ACE Query].[Archive Box Number]) Is Null) AND
(([Client ACE Query].[Archived Status])=No));

Max ACE Box Number Query gets the highest Archive Box Number value for
each client, so that the Update query can place this value in all of
the client's records in the Client ACE Query.

Thanks
Richard