DatePart And Between...And

Have been unable to find any solutions for this in the forum or the help files in Access.

Project has a [cutindate] and an [enddate].
A [flatamount] ($$ savings amount].

I am calculating the month savings by dividing the [flatamount] by the number of months between [cutindate] and [enddate] using DatePart().

With the help of someone on the forum I have a table "MonthOffset" which just has the numbers 1-12 in the [MonthOffSet] column. Using this, I can get my query to take the monthly savings amount and disperse it to each month for the next 12 months beginning with the [cutindate]. This was my original direction. They now don't want to see it dispersed over 12 months, but rather just show savings between the [cutindate] and [enddate]. I can get the correct monthly savings in my query, but it is still showing it for 12 months, i.e. [flatamount] is $50k, monthly savings is for 5 months, $10k showing for each of 12 months. I can just use this, then make another query to only show the monthly savings between the months I need but I would like to get it done in one query.

My query:

ProjectID CutInDate SavingsMonth
1 2/1/2006 2/1/2006
1 2/1/2006 3/1/2006
1 2/1/2006 4/1/2006
Through 1/1/2007

Project ID has a cut in date of 2/1/2006 but the savings will only last until 8/1/2006.

Has anyone had to do this before?

I have tried this in the criteria of my SavingsMonth field:

[MonthOffSet] Between DatePart("m",DateValue([cutindate]) AND DatePart("m",DateValue([enddate])))

But keep getting the message: You did not enter the keyword And in the Between...And operator. The correct syntax is "expression [Not] Between value1 and value2"

Does anyone know if this can be done?

Thanks,

Toni