Simulate VLOOKUP in Access

Hi all,

I have been looking at some of the posts on the whole issue of finding a VLOOKUP functionality from Excel for Access. And I have also read posts and MS Knowledge Base articles on the DLOOKUP function in Access. But what I need exactly is a little different, and was wondering if anyone can help me or share their experience:

I have a table in Access that contains values that should be looked up, for example:

VALUE1 INCREMENT
0.01345 -2.9
0.01391 -2.10
0.1439 -2.11

When using a VLOOKUP on this (if it was in an Excel table) the function should return the value INCREMENT.

In Access, this can be easily done with DLOOKUP, but I want the following functionality from it too (which comes from VLOOKUP in MS Knowledge Base):

Syntax for VLOOKUP
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

If VLOOKUP cannot find lookup_value and range_lookup is TRUE, VLOOKUP uses the largest value that is less than or equal to lookup_value.

Click to expand...

Therefore in my example above, if lookup_value is 0.01385(does not match any value in VALUE1 but is less than or equal to next largest value), it should return –2.10. How do I do this in Access? Is it possible using DLOOKUP?

Now I am going to make this thread a little bit hybrid: I am asking this previous question because I have an Excel spreadsheet that is used as a calculator.
But now I want to implement this in an Access form. In the Excel spreadsheet, there is a box where you enter a value, and then the answer appears in another cell. It uses statistical values in a table (using VLOOKUP) to get to this answer.
Now I am wondering, if its possible (and maybe easier) having my Access form link to this spreadsheet, and whatever gets entered into a textbox on the Access form, goes into the spreadsheet input cell, and returns the calculated answer to the Access form.

I have just started coding VBA mostly on Access, and any techies or gurus (or even novices) who have stumbled upon this issue – can you please give me some advice? Maybe there is some macro that can be written in Excel to accomplish this.

Regards,
Jean

Why even involve Excel at all if you don't have to? I have attached a zip file with 2k and 97 versions of a sample database which uses querydef and recordset objects to enumerate a query with subquery to achieve what you are attempting to accomplish. Check it out and see if it works for you.

Thanks ByteMyzer, the principle that you showed me works fine and I can apply it to my application.

I also wrote some code using SQL statements and Recordsets, but it was not as short and elegant as yours!

Regards,

Jean

Query (from the sample)


Please advise how you achieve this lookup.. it's perfect for what I am trying to do.

thanks,
Stewart

Simple single line code

I added another form same thing can be achieved through a simple single line code

Check text0 after_update event


Khawar