function that equivalent to vlookup in Excel

[Boon]

Hi,

When I work in Excel, I use function vlookup with "false match" (i.e.
vlookup(A1,B5:C5,2,FALSE). The formula gives me desired results.

I want to do this in Access. How can I do this? In the query design veiw, I
set the condition to be "like ....." but this does not work.

thanks,
Boon

 

[Michel Walsh]

You can use DLookup or, better, a join.

DLookup( "FieldNameWithTheNewDataYouWant", "tableNameYouLookInto",
"FieldNameToLook=" & valueToLookFor)


as example:


DLookup( "CompanyName", "Companies" , "CompanyID=" & 1010 )


returns the company name matching the company id value of 1010. Note that if
the field you look into for a match is not a numerical value, but a string,
you have to use the right delimiter, like:


DLookup( "CapitalCityName", "States" , "State=""" & "CA" & """" )


should return the capital of California, or Canada, or whatever.


A join is preferred if you use a query: in addition to your table, bring
the table you want to look into, and JOIN the fields to be look for:


SELECT ...
FROM yourTable INNER JOIN states WHERE yourTable.State =
states.StateAbbreviation

which, in graphical view, will simply show the two tables with a line
joining the two fields. You can then pick the fields, from the tables you
want, in the grid. It is like having the possibility of making a Lookup but
bringing back many fields, rather than just one. And it is generally faster,
since you don't pay the overhead of a function call, for each record.


Vanderghast, Access MVP