How can I use "Vlookup" within access

G

garry

I am trying to link access with an excel sheet, the sheet has a lot of
formula and vlookup etc. I need to know how to create the same from the new
report in access.
Thanks
Garry
 
G

George Nicholson

Within Access, Dlookup provides functionality very similar to that of
Vlookup:
Dlookup(FieldNameOfReturnValue, NameOfTable/QueryToSearch,
CriteriaSpecifyingRecordContainingReturnValue).
as opposed to Vlookup(CriteriaToLookFor, RangeToLookIn,
FieldToReturnValueFrom, ApproximateOrExact?)

Dlookup is more powerful than Vlookup, since you can specify criteria based
on multiple fields. Exact matches are pretty straightforward. If you are
looking for an approximate match, you'd have to do a little more work, just
like you do in Excel: you need to make sure the data being searched is in
the correct sort order to yield the results you want (i.e., provide the name
of a stored query that has an appropriate ORDER BY clause)
....or possibly use DFirst, DLast, DMin, DMax, etc. instead of DLookup.
....or open a recordset (sorted in a specific order) and then do a "Find
First" using the appropriate criteria.

If you are automating Excel from within Access, you can still use Vlookup
if you want: YourExcelObject.WorksheetFunction.Vlookup(...)

HTH,
 
P

Pat Hartman\(MVP\)

Although DLookup() does approximately what VLookup() does, Access isn't
Excel and the sooner you understand that the better. The correct technique
in a relational database is normally to base your form/report on a query
that joins the main table to the lookup table. DLookup()s are extremely
inefficient and should only be used in special cases.
 
K

Klatuu

Yes.

There is another possibility in this case. If you are linking an Excel
spreadsheet as a table, Access does not see the formulas - it sees only the
results; therefore, why not use the column in the spreadsheet that has the
VLookup in the report? Would it not already have the value you want?
 
Top