Access VLOOKUP equivalent

[JThornton]

I'm an EXCEL guy trying to learn ACCESS queries. Is there
an equialent function in ACCESS to the VLOOKUP function?
I have a price per item lookup where there can be as many
as 7 or 8 price breaks that can change frequently. I'd
rather not use "less than" or "greater than" functions.

 

[Rebecca Riordan]

If you're working in a query, add the PriceBreak table, make sure the
relationship is correct, and the query processor will handle the look up for
you. Otherwise, check the DLookup function, which is the equivalent of
VLOOKUP.

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

 

[Michel Walsh]

Hi,


In this case, it would be:


price = DMin("PPI", "tablenameHere", "Volume<=" & QtyRequired )


So, if the required quantity is 2700, only the first two prices would be
kept, and the minimum on these would be returned, 22.50. In fact, you may
ask why it was a DMin and not something else? It could have been something
else, but that is the nature of the problem. As example, it may have been
required to interpolate, as if it was a curve (x, y) rather that discrete
jumps and in that case, other formulation would have been used. It just
happen that your case is a simple one that DMin solves.


Hoping it may help,
Vanderghast, Access MVP