Efficiency Question

[Steve S]

What is more efficient, 2 DLookups (method X) or 1Dlookup and a Right()
and a Left() (method Y).

What I currently have is the table below and I use method Y and break out
the right 3 characters from field D and then the 3 left characters.


A B C M
1 2 9 130-200
1 0 1 030-200
2 3 3 150-210
2 2 2 130-200
2 0 1 030-200
2 4 9 220-230

Would it be better to break field M into 2 fields -Mright and Mleft- and use
2 Dlookups. Or does it even make any difference?

 

[Allen Browne]

I still use them occassionaly, David.

Particularly when I need an updatable query.

 

[ErezM via AccessMonster.com]

hi
1 dlookup to retreive the whole field, and then right(x,2) and left(x,3), is
much faster than 2 dlookups
but, if you have use for the seperate values anyway, consider splitting them
in the table (and add them back together only for displaying purposes)

Erez

 

[David W. Fenton]

I still use them occassionaly, David.

Particularly when I need an updatable query.

Subqueries can often be used to avoid that problem, though they
might not be optimized (calls to DLookup() in each row can't be
optimized, either).