423,850 Members | 1,069 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,850 IT Pros & Developers. It's quick & easy.

need replacement for DLookup in UPDATE query

P: n/a
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

Mar 31 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
DFS
bo*********@yahoo.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?

Mar 31 '06 #2

P: n/a

DFS wrote:
bo*********@yahoo.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

Apr 4 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.