Lookup of result based on 1 value compared to range.

[Scorpio11]

Hi

I am having a issue with lookups and all the sites that i can see don't seem to deal with it the way i am wanting to

I want to perform a look up on based on a number and i want it to pull a record based on a range.

Eg: Value i have = 22 is in 1 table and i want it to find the record based on the range that starts at 21 and ends at 30 out of the following range of records and give me the result C that exists in another table

ID Start End Result
1 0 10 A
2 11 20 B


3 21 30 C
4 31 40 D

My problem is that i cannot figure out how to do this, queries seem obvious, but i cannot program it properly. SQL also does not seem to help me (although i am hardly an expert at it). VBA would seem the best, but i have no idea how to code.

All the usual sources of looks up deal with combo boxes or linking tables together. But those rely on precise lookups. I am needing an imprecise lookup based on range.

Thoughts, comments, suggestions all welcome.

Thanks - Scorp

[June7]

Try:

DLookup("Result", "tablename", criteria input & " BETWEEN Start AND End")

Trick is capturing the criteria input. It can be reference to another field of a query or to a textbox on a form.

[Scorpio11]

Hi,

I created a VBA code with your Dlookup suggestion, i just put in my tables and cells now. I put it in a button so when i click it, the result is produced.

My exact code is:

Me!SearchResult = DLookup("TestRes", "tblLookup", criteria input & " BETWEEN TestValSt AND TestVal")

I am getting an error though:

Compile Error
Expected: list separator or )

[Scorpio11]

Never Mind, I have just figured it out, just made an ass of myself

Me!SearchResult = DLookup("TestRes", "tblLookup", SearchString & " BETWEEN TestValSt AND TestVal")

I just realised that I was not comparing it to anything,

Thanks a mil.

[June7]

Need something in place of 'criteria input' for user input. This could be reference to control on form or an InputBox function call or a field in query.

Instead of VBA code could put the expression in a query to create a field or in ControlSource of textbox. The 'criteria input' would be the field that has value 22.

Okay, you figured that out while I was posting.