Dlookup with dyamic "between" criteria stored in table

[Bkper087]

Is it possible to reference a table to check if a value is between two numbers and return a related field? I am using Dlookup, but I’m not sure what would go in the criteria field. I tried just using a relationship, but I cant figure out a way to make it work.

Here is an overview of related info:


I would like to display different messages based on a number. This number represents the days that have passed since the record was created. This number is currently in the query: qryClients

I have also created a table: tblMessages that has the fields ID, Message, DaysAging. This table stores the message I would like to have appear based on the number of dates that have passed.

I would assume that Dlookup would be best for this function, but since it would not be an exact match but rather a between type function, I’m not sure what that would look like. Any help would be appreciated.

[aytee111]

Can you give examples of what you are expecting and what the table contains for Days?

[ranman256]

Dlookup is for forms.
queries use joins on tables.

the query can find items if the table has a min and max

msg, min, max
low, 0, 10
med, 11, 20
hi, 21, 50

youd place your lookup table in the query ,NO JOINS, and criteria would as [field]>=tLookup.min and [field]<= tLookup.max