Lookup values (1 Viewer)

[A real begineer]

I am sure this is really simple, however, as often happens I just don't get it.

I have a field called cal which is updated by the user at random intervals based on the previous input.

Now what I need to do, is find the value of the last entry, which could be anywhere. For this I use:
varC = DMax("[cal]","[Client Details]")
I then establish the id of this field using a simple variation on above:
varCI=Dmax("[ID]","[Client Details]","[cal]=" & varC)
This part works fine.

What I would like to do is now look up the three previous readings before varCI. I have tried various combinations but just don't seem to be able to pull up the id number, which prevents me limiting the search.

My questions are:
Am I looking at the appropriate tool to do this job?
How do I work my code to make this happen?

My results should be something like:

ID 280 Cal 340
ID 260 Cal 300
ID 233 Cal 320
ID 210 Cal 300
etc

All this merriment is in Access 2000.

 

[ntp]

Run this query to limit your values:

strSQL = "SELECT TOP 4 [ID], [cal] FROM [Client Details] WHERE [cal] <= " & varC & " ORDER BY [ID];"

This SQL code will return all records whose [cal] vales are less than or equal to the varC value. The TOP 4 predicate will limit the returned records to four.

ntp