A query field "occurence" includes data that ranges from 0 to 10,000,000. I want to look up the corresponding "bi limit code" from a seperate table and store the lookup value as a field in this same query.

Occurence...... BI Limit Code
0 - 100,000....................10
100,001 - 150,000.......... 55
> 150,000..................... 60

For example, if the occurence field value is 120,000, the lookup value would be 55. If the occurence field value is 160,000, the lookup value is 60. How can I do this by using a simple function or expression ? I'd prefer to not use any SQL language.

The lookup table needs three columns:
FromAmt
ThroughAmt
BiLimitCode

To lookup the limit:

Me.SomeField = DLookup("BiLimitCode", "YourTable", "FromAmt >= " Me.SomeAmt & " AND ThroughAmt <= " & Me.SomeAmt)

Thank you sir.