Returning value using date range

I am having issues with returning a value when a date in one table is between two dates in another table.

Here are the fields of the two tables (simplified to the ones used for this query)
The two tables do no share a relationship based on IDnumber.

Table1:
IDNumber (primary)
AcctNumber
Activation Date

Table2:
IDNumber (primary)
AcctNumber
Date1
Date2
Percentage

I need to return Table2.Percentage when Table1.AcctNumber equals Table2.AcctNumber AND Table1.ActivationDate is BETWEEN Table2.Date1 AND Table2.Date2


On the surface this appears like it should be easy. I feel that I am missing something simple. Does anyone have any suggestions. Should I use VB? SQL?

Please help. Thanks!

You might be able to use a simple Select Query using the following SQL for the query.

SELECT Table1.AcctNumber, Table2.Percentage
FROM Table1 INNER JOIN Table2 ON Table1.AcctNumber = Table2.AcctNumber
WHERE (((Table1.AcctNumber)=IIf([ActivationDate]>[Date1] And [ActivationDate]<[Date2],[Table1].[AcctNumber])));

This will give you the AcctNumber from Table1 and the corresponding Percentage from Table2

lwells