Extract date periods for calculation

[robin a]

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

[ADezii]

The Number of Periods can be calculated using a Custom Function that is passed the Start and End Dates as Arguments. The Function would be embedded in a Query and would be called for each Record in the underlaying Table returned the requested Number of Periods. If you wish to pursue this approach let us know.

[robin a]

Yes I would. thank you

[ADezii]

Yes I would. thank you

As soon as I get a chance, I'll see what I can come up with. BTW, what are the possible Year Ranges, namely: 2008 to 2013, 2009 to 2022, etc.?

[robin a]

thank you. another direction that popped in my head is to count increments of one between the two period dates but then how would it count between 11.09 and 2.1, which should be 2? thanks again.

[NeoPa]

Robin, have you noticed that year 2010 represented by a simple 1 bears no discernable arithmetic relationship to the codes for all the other years. It seems like someone had the idea to store this data, and a separate idea that it could be stored as a floating point number. Both are true, but the data doesn't show in a recognisable format when it's representing 2010.

[robin a]

what do you suggest? i didn't create the database but I can change things.

[NeoPa]

To determine the number of periods is relatively simple, but complicated by having to use Format() in what appears to be a numeric field. It would make much better sense to use a string value of either 4 or 5 characters (4 if you dispense with the '.' which is largely unnecessary). However, for now we're dealing with a variable length floating point field.

Periods: Int([End Date])-Int([Start Date])+13*(Val(Right(Format([End Date],'0.00'),2))-Val(Right(Format([Start Date],'0.00'),2)))+1

PS. Having now read your earlier reply I would clarify my suggestion.

I would format a 4 or 5 character string with the two-digit year in the first two positions and the period within the year (01 - 13) in the last two. Use a '.' in between only if you feel it is necessary for display/understanding purposes.

[robin a]

You are e genius! it works perfet. I can't thank you enough!

[NeoPa]

Actually, the format would be easier to process if the dot (.) were in place after all.

General formula to handle either format :

Periods: 13*(Val(Left([End Date],2))-Val(Left([Start Date],2)))+Val(Right([End Date],2))-Val(Right([Start Date],2))+1

Specific formula that requires the dot (.) :

Periods: 13*(Int([End Date])-Int([Start Date]))+Val(Right([End Date],2))-Val(Right([Start Date],2))+1

PS. We keep cross-posting :-D
I would suggest you make the change to your design to store this value in a 5-char string as covered in this post. It is possible (obviously) to process the previous format, but clumsy and misleading. I would certainly predict maintenance issues in future if you leave it as it was.

PPS. ADezii is one of our foremost experts. He tends to favour VBA solutions and is a great man to have helping when you can get him. Naturally enough, he will tend to offer his assistance on that side of things where he can help, but not all situations are best handled in code. All that said, I'm sure any solution he offered would handle your problem perfectly well.