Vlookup

  • Thread starter Thread starter DKS
  • Start date Start date
D

DKS

Anyway of simulating a VLOOKUP kind of function via Access Queries.

For users who are not familiar with VLOOKUP, it functions as follows:

Amongst a table of entries (recordset), a certain "input" value is looked up
in the first column. The first record where the values match is selected,
and via a second parameter the column from which to display the value is
selected.

Many thanks in anticipation.
 
I'm not sure if I understand what you are asking correctly, so forgive me if
this is very rudamentary.

in Excel, you are basically wanting the content of a particular cell to be
the value foudn somewhere else, so cell D3 might say =Vlookup("red",colD,colF)
or in other words, D3 will be populated with the value in colF on the row
where red is in colD

In Access you would say
Select colF from someTable where colD = "red"
 
Hi Rolaaus

What you mentioned is correct. But excel gives on extra feature, using the
last parameter of Vlookup. You may ask for either an exact match (in which
case Access will work as you mentioned). But you can also ask for an entry
that falls within two possible values, i.e. if an exact match is not found
then the entry that falls next is provided.

Hope this helps.
 
Hi Rolaaus

What you mentioned is correct. But excel gives on extra feature, using the
last parameter of Vlookup. You may ask for either an exact match (in which
case Access will work as you mentioned). But you can also ask for an entry
that falls within two possible values, i.e. if an exact match is not found
then the entry that falls next is provided.
Click to expand...

The third parameter of DLookUp needs to be a text string which evaluates to a
valid SQL WHERE clause. This can be almost arbitrarily complex, probably as
powerful or more powerful than VLookUp. A third parameter doing what you
describe would be

"[DateField] >= DateSerial(Year(Date()), 1, 1) AND [DateField] <
DateSerial(Year(Date()) + 1, 1, 1)"

to get all records in the current year; or

"FirstName LIKE '[A-K]*' AND Month([DOB]) = 7"

to find all names starting with A through K with birthdays in July.


John W. Vinson [MVP]
 
Back
Top