Yes, it would be possible to DLookup() the record once you have the value to
find, but that's starting to get fairly inefficient. What we do is use a
replacement for DLookup() which is much more efficient. It includes an extra
argument for specifying a sort order, so you can lookup one field but sort
by something else.
Paste the function below into a standard module, and save.
You can then use this expression to get the Unique ID in one step:
=ELookup("UniqueID", "MyTable", , "Abs([SumField] - " & [SomeValue])
------------code starts----------------
Function ELookup(Expr As String, Domain As String, Optional Criteria,
Optional OrderClause)
On Error GoTo Err_ELookup
'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
Exit_ELookup:
Set rs = Nothing
Set db = Nothing
Exit Function
Err_ELookup:
MsgBox Err.Description, vbExclamation, "ELookup Error " & Err.Number
Resume Exit_ELookup
End Function
------------code ends----------------
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Scott said:
Thanks, now this returns the minimun difference value, but I need something to
return the Unique ID at which this MDV occurred. Im assuming I could use a
Dlookup on the result but am having trouble with the format
Allen said:
You want the minimim difference from a value, disregarding sign:
=DMin("Abs([SomeField] - " & [SomeValue] & ")", "MyTable")
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Scott said:
Anyone know a function, like dlookup that would return the closest value
in a field depending on the criteria? I know dlookup will do this when
the criteria is exactly met, but I need it to return a value from the
record that is CLOSEST to this
