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
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