Simulate Vlookup in Access?

  • Thread starter Thread starter Lee Hunter
  • Start date Start date
L

Lee Hunter

Is there a way to lookup a value in a database and get an
an "approximate" match, such as in:
VLOOKUP(lookup_value,table_array,col_index_num) With
neither "TRUE" nor "FALSE" specified.

As I undertand, Dlookup only finds exact matches.

Nested IIF statements are too complex with more than 10
values.
 
The 3rd argument of DLookup() can be something like:
"[MyField] > 64"
so it is not an exact match.

If you want the nearest match, DLookup() is not up to the job.
There is an extended-lookup function in this link:
http://members.iinet.net.au/~allenbrowne/ser-42.html
It has an extra argument that lets you specify a sort order.
You can get the record nearest to 64 by sorting on:
"Abs([MyField] - 64)"
 
Hi Lee,

DLookup should work fine. The syntax is a little different from the
VLookup workseet function:

DLookup(expr, domain[, criteria])

Think of DLookup as a kind of query in miniature. "expr" is the name of
the field being queried; "domain" is the set of records that are of
interest (usually a table or query name), and the optional "[criteria]"
is used to restrict the data returned by the function. It's kind of like
a WHERE clause. An example would be:

DLookup("[Field]" , "
" , "[Field] IN('True','False') ")


For an "approximate match", something like:

DLookup("[Field]" , "
" , "[Field] LIKE '*' & <criteria> & '*'")

hth,

LeAnne
 
Back
Top