Lookup Next Value In Range
Jim Anderson
table if the actual value being searched is not found. In other words,
assume a table contains the values 1.16, 1.50, 2.77, & 3.44. If I lookup
1.50, the value returned is 1.50. However, if I lookup 1.52, the value
returned is 2.77.
I used to use a program from Symantec called Q&A. It had a built in
function called lookupr (lookup range). i have not found how this is
accomplished in Access. Any suggestions?
Mark Taylor
Can't help with lookup but how about opening a recordset sorted ascending on
your value,
do a findfirst with criteria value>= 1.5 (as in your example)
Mark
Joe "Nuke Me Xemu" Foster
>I would like to perform a lookup which will find the next higher value in a
>table if the actual value being searched is not found. In other words,
>assume a table contains the values 1.16, 1.50, 2.77, & 3.44. If I lookup
>1.50, the value returned is 1.50. However, if I lookup 1.52, the value
>returned is 2.77.
>I used to use a program from Symantec called Q&A. It had a built in
>function called lookupr (lookup range). i have not found how this is
>accomplished in Access. Any suggestions?
Function LookupR (ByVal F As String, ByVal T As String, ByVal V As Variant) As Variant
On Error Goto LookupRError
Dim SQL As String: SQL = "select min(" & F & ") from "
If Instr(1, T, "[", 0) > 0 Then SQL = SQL & T Else SQL = SQL & "[" & T & "]"
SQL = SQL & " where " & F & " >= "
Select Case VarType(V)
Case vbEmpty, vbNull
LookUpR = V: Exit Function ' oh, a wise guy, eh?
Case vbCurrency
SQL = SQL & "ccur(""" & V & """)" ' ccur obeys regional settings
Case vbDate
SQL = SQL & "cdate(" & Str$(CDbl(V)) & ")" ' for subsecond precision
'SQL = SQL & Format$(V, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
Case vbString
SQL = SQL & "'" & StrReplaceAll(V, "'", "''", 0) & "'"
Case vbDecimal
SQL = SQL & "cdec(""" & V & """)" ' cdec obeys regional settings
Case Else
SQL = SQL & Str$(V)
End Select
Dim DB As Database: Set DB = DBEngine(0)(0)
Dim RS As Recordset: Set RS = DB.OpenRecordset(SQL, dbOpenForwardOnly)
If RS.RecordCount > 0 Then LookUpR = RS(0) Else LookUpR = Null
RS.Close: Set RS = Nothing
Set DB = Nothing
Exit Function
LookupRError:
Dim E As Long: E = Err
On Error Resume Next
If Not (RS Is Nothing) Then RS.Close
Set RS = Nothing
Set DB = Nothing
Error E
End Function
Get StrReplaceAll at <http://members.ricochet.net/~jfoster/>.
Or, you could just use DMin("fld", "tbl", "fld >= 1.52"), but the
built-in domain functions suck. I've been meaning to update my
FormatSQL function anyway...
--
Joe Foster <mailto:jfo...@ricochet.net> Space Cooties! <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!
Radu Lascae
I don't know how Q&A works. I also don't know exactly what you mean by a
"lookup". You may use the FindFirst method of the recordset object with
criteria set >= 1.50. Or you may use a query with values between two given
values. Or a couple of other things. What exactly do you want to do?
Radu Lascae
gHaD
> I would like to perform a lookup which will find the next higher value in a
> table if the actual value being searched is not found. In other words,
> assume a table contains the values 1.16, 1.50, 2.77, & 3.44. If I lookup
> 1.50, the value returned is 1.50. However, if I lookup 1.52, the value
> returned is 2.77.
>
> I used to use a program from Symantec called Q&A. It had a built in
> function called lookupr (lookup range). i have not found how this is
> accomplished in Access. Any suggestions?
You could write a function that does a FindFirst, returning the record you
want. If the rst.NoMatch is true, then you could have it return a DMax() or
something similar...
--
+++++++++++++++++++++++++++++++++++++++
+++ gHaD (Aaron Robinson) +++
++ MJM-PC Solutions, Inc. ++
++ Chicago, IL ++
++ ++
++ -> ICQ# 5686832 <- ++
++ Before they invented drawing ++
++ boards, what did they go back to? ++
+++++++++++++++++++++++++++++++++++++++