extracting date periods to use in calculation

[robina]

I have a query in which I need to get the number of periods between and including the start date and end date for a project to use in a calculation. There are 13 periods in a year. I can't use exact dates for the periods because they change each year. The period is a field in a table and is stored like this:
Starte Date End Date
1.1 3.1
2.1 5.11
3.09 2.1
1.1 is period 1 in 2010, 3.09 is period 3 in 2009, 5.11 is period 5 in 2011 etc.

I then have a calculation that uses the number of periods between and including the start date and end date.
example:

start date 11.09 end date 3.1 would be 6 periods because you count the beginning period and include all the way to period 13 for that year then 3 periods for 2010, equalling 13. I'm great with the calculation but I cant figure out how to get the number of periods when an end date year is different than the start date year. its not a problem in the same year.

thank you

 

[plog]

I can't use exact dates for the periods because they change each year.

You're definition of exact date isn't an exact date. You need a table like this to define your periods:

period_num, period_begin, period_end
1, 11/17/2008, 12/15/2008
2, 12/16/2008, 1/10/2009
3, 1/11/2009, 2/8/2009

Then if you want to know how many periods occur between 2 exact dates (i.e. 2/14/2010 and 4/18/2011) it becomes a Dcount (http://www.techonthenet.com/access/functions/domain/dcount.php).

 

[pr2-eugin]

EDIT: Removed codes..

 

[plog]

I'd advise against giving people a fish when what they really need is to learn how to fish for themselves. He's going to take your solution, plug it in, get past this issue and then in a week he will have another issue based on his improperly structured database and be back. Or worse, he will need to slightly tweak your code and have no clue what it does or where to make that tweak.

 

[pr2-eugin]

Yes plog, I should not have done that..

I have learned so many lessons about Databases by heeding to advice given to me.. So I should not take the same opportunity away from someone else.. So I have removed my 'work-around' code.. Sorry about that..