Alternative to Vlookup in Access 2002

[ray]

Hello All,

I'm trying to replace the vlookup function in a Excel
Worksheet with a query in Access.

Within Access, I have created two tables containing the
data and join them. Naturally, the query ignore those
records without a exact match.

My dilemma is when the Vlookup doesn't find a perfect
match it picks the closest value.

How to do you replicate the Vlooup behavior of finding the
closest value within a query or DAO.
Please advise.

Ray

 

[Allen Browne]

DLookup() is generally the lookup function in Access, but it is a bit
limited for what you want. You need to retrieve a value from a field where
another field is greater than or equal to the threshhold.

Here's an extended lookup function named ELookup(). It's about double the
speed of DLookup(), and allows you to specify an OrderClause so you can get
the correct match.

This example retrieves AnswerField from MyTable, where SomeField reaches the
threshold value, and since it's sorted by SomeField you know you are getting
the desired AnswerValue:
=ELookup("AnswerField", "MyTable", "SomeField >= " & [SomeValue],
"SomeField")


Function ELookup(Expr As String, Domain As String, Optional Criteria,
Optional OrderClause)
'Purpose: Faster and more flexible replacement for DLookup()
'Arguments: Same as DLookup, with additional Order By option.
'Author: Allen Browne. (e-mail address removed)
'Examples:
'1. To find the last value, include DESC in the OrderClause, e.g.:
' ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
'2. To find the lowest non-null value of a field, use the Criteria,
e.g.:
' ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname")
'Note: Requires a reference to the DAO library.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

'Build the SQL string.
strSQL = "SELECT TOP 1 " & Expr & " FROM " & Domain
If Not IsMissing(Criteria) Then
strSQL = strSQL & " WHERE " & Criteria
End If
If Not IsMissing(OrderClause) Then
strSQL = strSQL & " ORDER BY " & OrderClause
End If
strSQL = strSQL & ";"

'Lookup the value.
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset(strSQL, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
ELookup = Null
Else
ELookup = rs(0)
End If
rs.Close

Set rs = Nothing
Set db = Nothing
End Function