Access Access: DLOOKUP and Between..And

[Buddha247]

I have a field in Table A that requires a value from Lookup Table B. I am comparing a field value in Table A with a BeginNumber and EndNumber fields in Table B. If the Table A value is found in Table B, then I want to populate another field in Table A with a corresponding value in another field in Table B beside the Range Numbers.

Table A

Test_ID
NewValue

Table B - Multiple ranges of BeginNumber and EndNumber

BeginNumber
EndNumber
RangeValue

If Test_ID is found in a range between BeginNumber and EndNumber, then populate NewValue in Table A with the corresponding value in RangeValue.

I was using DLOOKUP and trying to use a Between..And expression, but I cannot seem to get it to work.

Can someone help sort this out. Is there a better way of doing this? I cannot use a IIF statement, because there is only a many-to-many relationship between two related fields between the tables, and If I use that, I get the matching Range, and all the other failed "False" which grows my results by the number of ranges in the Table B.

Thanks