DatePart and Between...And

[thart21]

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

 

[Brianwarnock]

I cant say that I really understood this but as nobody else has replied I will just make a couple of observations

1 to divide [flatamount] by the number of months between 2 dates I would use [flatamount]/datediff("m",cutindate,enddate)

2 the error in the Between...And is the field name at the start. I didn't understand what you where trying to do but that is syntactically incorrect.

Brian

 

[thart21]

Thanks Brian, that took a step out of my monthly calculation query. The 2nd part of my problem is how to get it to show in the query only the months from cutindate to enddate instead of 12 months. What I was trying to do in the Between..And was to take the cutindate and add to it from MonthOffSet, the number of months to the enddate, i.e. cutindate 6/1/2007, enddate 10/1/2007. Query should only show 6/1/2007, 7/1/2007, 8/1/2007, 9/1/2007 and 10/1/2007. With this code - DateAdd("m",[MonthOffSet],[cutindate]) it is giving me twelve months from the cutindate.

Thanks for the help.

Toni

 

[Brianwarnock]

I don't think you can do this in a simple query. I think that you will need to resort to code as what you are trying to do is

dateadd("m",1,cutindate)
If enddate > cutindate
write data
loop

Unfortunately my VBA is too rusty for me to attempt this and now that I am retired I have access to no literature.
I would reword your requirement and post in the modules and VBA thread

Brian