Year wise Date spliting

[rohinikrishna1987]

Sir I have table with two field: [STARTDATE] and [ENDDATE], Suppose the start date may be 15/05/2005 and the enddate may be 25/8/2009. Now I want to split these days like following;
15/05/2005 to 31/12/2005
1/1/2006 to 31/12/2006
1/1/2007 to 31/12/2007
1/1/2008 to 31/12/2008


1/1/2009 to 25/08/2009
The above details may be get in a table or a query with two fields. How can i do this with access. I am not efficient in VBA. Please help me

[rohinikrishna1987]

Sir I have table with two field: [STARTDATE] and [ENDDATE], Suppose the start date may be 15/05/2005 and the enddate may be 25/8/2009. Now I want to split these days like following;
15/05/2005 to 14/05/2006
15/05/2006 to 14/05/2007
15/05/2007 to 14/05/2008
15/05/2008 to 14/05/2009
15/05/2009 to 25/08/2009
These type of splitting is also needed

[CJ_London]

you will need another field to indicate which type of split you require

you will also need another table, perhaps based on years and used in a cartesian query with your current table

until your requirement is perfectly clear, difficult to be more specific.

[rohinikrishna1987]

Ajax sir.... I am always thankful to u. Your query (AJAXQ, I named that in my software) for my previous post (ACRSTDT,ACRENDT) is working well...and is used in my office, That software is a success one..all officials appreciating me for developing such a handy software...But i know all its working is only with your magical query which you sent me..thank u sir..
Now I am trying to develop another software for calculating earned leave of a Govt.Employees (Earned leave means; Employee earned One EL, while in duty for 11 days, i.e an employee will earn 33-2/11 EL in a year 33-3/11 in a leap year), While retiring an employee we have to calculate the balance EL for surrendering the same for money. I have almost succeeded. But the result is only summery. We also want the detailed report of the leave details, ie for each year, that is why I post such a thread ..Now I need only the answer of the following question.
Sir I have table with two field: [STARTDATE] and [ENDDATE], Suppose the start date may be 15/05/2005 and the enddate may be 25/8/2009. Now I want to split these days like following;
15/05/2005 to 14/05/2006
15/05/2006 to 14/05/2007
15/05/2007 to 14/05/2008
15/05/2008 to 14/05/2009
15/05/2009 to 25/08/2009
I am attaching some scree shot of my half cooked project! Thanking you sir

[rohinikrishna1987]

Screen shot

[rohinikrishna1987]

Screen shot continuing

[rohinikrishna1987]

Sir I want to get result like this

[rohinikrishna1987]

you will need another field to indicate which type of split you require

you will also need another table, perhaps based on years and used in a cartesian query with your current table

until your requirement is perfectly clear, difficult to be more specific.

Sir Please help me

[CJ_London]

I do not need to see screen shots, I need to see table and field names, and relationships

So best guess per the requirement in post#4

1. create a table called CTR. It contains a single number field (long type) called Counter and should be populated with 0,1,2,3 etc up to a maximum number of your deciding - based on your example suggest 10 might be enough

2. next create this query - change names to suit

SELECT DateAdd("yyyy",[counter],[startDate]) AS Date1, IIf(DateAdd("yyyy",[counter]+1,[startDate])<[endDate],[endDate],DateAdd("yyyy",[counter]+1,[startDate])-1) AS Date2
FROM CTR, myTable
WHERE (((Counter)<=DateDiff("yyyy",[startDate],[endDate])))
ORDER BY Counter


I do not have much free time right now so if this is not what you require, or there are other factors you have not mentioned, you will need to work it out for yourself

good luck with your project

[rohinikrishna1987]

I do not need to see screen shots, I need to see table and field names, and relationships

So best guess per the requirement in post#4

1. create a table called CTR. It contains a single number field (long type) called Counter and should be populated with 0,1,2,3 etc up to a maximum number of your deciding - based on your example suggest 10 might be enough

2. next create this query - change names to suit

SELECT DateAdd("yyyy",[counter],[startDate]) AS Date1, IIf(DateAdd("yyyy",[counter]+1,[startDate])<[endDate],[endDate],DateAdd("yyyy",[counter]+1,[startDate])-1) AS Date2
FROM CTR, myTable
WHERE (((Counter)<=DateDiff("yyyy",[startDate],[endDate])))
ORDER BY Counter


I do not have much free time right now so if this is not what you require, or there are other factors you have not mentioned, you will need to work it out for yourself

good luck with your project

I wont disturb you in future sir.......

Thank u for every things sir, Your query is working super sir ....first time it was show some error, then i checked it and I have replace < to > then it working superb thank u sir

ELECT DateAdd("yyyy",[counter],[startDate]) AS Date1, IIf(DateAdd("yyyy",[counter]+1,[startDate])>([endDate],[endDate],DateAdd("yyyy",[counter]+1,[startDate])-1) AS Date2
FROM CTR, myTable
WHERE (((Counter)<=DateDiff("yyyy",[startDate],[endDate])))
ORDER BY Counter