Vlookup vs. Dlookup

[Guest]

I am working on a project that involves two tables. In the tables i have
distance vs. amount. One table is recent the other old. I need to be able
to enter a number for a recent value and have it give me the closest value in
the old. I alrady did this in Excel. The problem is that in excel it
returns the highest number that is less than. Which is not always the
closest number to the value. Is there a way to get the Dlookup in access to
give me the closest number regardless of whether it is greater or less than.

 

[Graham Mandeno]

Hi Keith

The easiest way to do this is to write your own function which selects the
record with the smallest difference between the compare field value and the
lookup value:

Public Function LookupClosest( _
LookupValue As Variant) _
As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT TOP 1 [Field to return] FROM [Table name] " _
& "ORDER BY Abs([Field to compare]-" & LookupValue & ");"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
LookupClosest = Null
Else
LookupClosest = rs(0)
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End Function

You fill in the obvious bits (field names and table name).

You could modify the function so that these three values are passed as
arguments, and then it could be used for any sort of closest lookup.

 

[Wayne Morgan]

Tried,
DLookup("[FieldNameInOldTable]", "[OldTableName]", "Min(Abs(" &
[CurrentValueField] & " - [FieldNameInOldTable]))")
but it won't allow an aggregate function in the Where clause. The help file
doesn't say this, it only says you can't use it for the first argument. I
believe it is because SQL won't allow it.

I was able to do it with the following SQL expression:

SELECT TableOld.Field1
FROM TableOld
GROUP BY TableOld.Field1
HAVING (((DMin("Abs([Field1]- " & [Forms]![Form1]![txtNewValue])",
"TableOld"))=Abs([Field1]-[Forms]![Form1]![txtNewValue])));

 

[Guest]

thanks guys for the help. it worked

Tried,
DLookup("[FieldNameInOldTable]", "[OldTableName]", "Min(Abs(" &
[CurrentValueField] & " - [FieldNameInOldTable]))")
but it won't allow an aggregate function in the Where clause. The help file
doesn't say this, it only says you can't use it for the first argument. I
believe it is because SQL won't allow it.

I was able to do it with the following SQL expression:

SELECT TableOld.Field1
FROM TableOld
GROUP BY TableOld.Field1
HAVING (((DMin("Abs([Field1]- " & [Forms]![Form1]![txtNewValue])",
"TableOld"))=Abs([Field1]-[Forms]![Form1]![txtNewValue])));

--
Wayne Morgan
MS Access MVP

I am working on a project that involves two tables. In the tables i have
distance vs. amount. One table is recent the other old. I need to be
able
to enter a number for a recent value and have it give me the closest value
in
the old. I alrady did this in Excel. The problem is that in excel it
returns the highest number that is less than. Which is not always the
closest number to the value. Is there a way to get the Dlookup in access
to
give me the closest number regardless of whether it is greater or less
than.