Dlookup Next Record

[illini]

I have a table of various numeric figures. I would like to use the dlookup funtion to find a field based on a number. Specifically, I'd like to set it up to either find the record, or (if the record doesn't exist) the record of the next higher number.

In other words, I'd like to use it similar to the vlookup function in Excel. Any idea on how to set it up?

-illini

 

[DougP]

If you use SQL its much more powerful and 10 times as FAST.
but it requires more code.

this is the minimum you need
just copy it and save it an use it over and over.

    Dim Conn2 As ADODB.Connection   'create connection
    Dim Rs1 As ADODB.Recordset      'create recordset
    Dim SQLCode As String           'place to store SQL string
    Set Conn2 = CurrentProject.Connection  ' <<<<Note same as CurrentDb
    Set Rs1 = New ADODB.Recordset
    SQLCode = "SELECT * FROM [yourTable] WHERE [yourfield]>= " & somenumber & ";"
    Rs1.Open SQLCode, Conn2, adOpenStatic, adLockOptimistic
    debug.print Rs1![yourfield]

'  close it this way
    Set rs1 = nothing
    Set Conn2 = nothing

OK
so 90% of remains the same all the time the line you change is this:
SQLCode = "SELECT * FROM [yourTable] WHERE [yourfield] >= " & somenumber & ";"
Of course yourTable is your actual Table name just like in Dlookup and yourfield is your field name just like in Dlookup and somenumber is likewise.
the SQLcode line, this is where it becomes real powerful though unlike Dlookup because there is “basically” no limit to what you can ask or do.
you can choose as many fields as you want and <> or = or "LIKE" or tons of other compares remember also you can have multiple tables too.
Easy way to build a SQL string is ue the QBE grid to make a query. There is a little know SQL tab in the query maker that creates the SQL for you so you just copy and paste it in above. Well almost.

Now the results are returned in here
Rs1![something] the something is any field in the table. So you can say X = Rs1![something]

Good luck

DougP, MCP, A+

 

[Zion7]

Illini, I agree implicitly with DougP. What he showed you, is potentially, much more dynamic & versatile than, what I'm going to show you.
But, just to answer your question...

Sub NextHighest()

Dim i, x, y As Long: x = 10000

Do Until i > 0

i = Nz(DLookup("txtPopulation", "tblCountries", "txtPopulation = " & x), 0)

If i = 0 Then
x = x + 1: y = y + 1
End If
Loop

Debug.Print "Result = "; i & " Times Looped = " & y

End Sub

If necessary, if you have any problems understanding the code, I'm sure DougP or myself, will be happy to elaborate.

Good Luck either way!