Months automatic breakdown breakdown (1 Viewer)

[Capitala]

Good day!
I'm working on tenancy contract project. I enter the start date and end date manually. I need a code to break down tenancy contract to periods automatically.
i.e. if the contract starts on 6-12-2020 and ends on 15-5-2020, it should break them down as follows:
period 1: from 5-12-2020 to 4-1-2021, period 2: from 5-1-2021 to 4-2-2021

I a need a code to do that.
Thanks in advance

 

[theDBguy]

Hi. Welcome to AWF!

Did you want the code to return just one period or all of them? If all of them, do you have a table for storing them?

 

[moke123]

Take a look at the DateAdd()
https://www.techonthenet.com/access/functions/date/dateadd.php

 

[Capitala]

Hi. Welcome to AWF!

Did you want the code to return just one period or all of them? If all of them, do you have a table for storing them?

Thanks for your kind support. I have a table already for that

 

[theDBguy]

Thanks for your kind support. I have a table already for that

Can you show us the table structure?

 

[Capitala]

Can you show us the table structure?

It should be like that, where the start date and end date will be taken from external fields

Contract_id	Period	From	To
1	1	5-1-2020	4-2-2020
1	2	5-2-2020	4-3-2020
1	3	Etc.	Etc.

 

[moke123]

you could do something along these lines:

Sub OneYear(dteStart As Date)

    Dim i As Integer
    Dim dteS As Date
    dteS = dteStart

    For i = 1 To 12
        Debug.Print i, dteS, DateAdd("m", 1, dteS) - 1
        dteS = DateAdd("m", 1, dteS)

    Next i

End Sub

 1            1/5/2021      2/4/2021
2            2/5/2021      3/4/2021
3            3/5/2021      4/4/2021
4            4/5/2021      5/4/2021
5            5/5/2021      6/4/2021
6            6/5/2021      7/4/2021
7            7/5/2021      8/4/2021
8            8/5/2021      9/4/2021
9            9/5/2021      10/4/2021
10           10/5/2021     11/4/2021
11           11/5/2021     12/4/2021
12           12/5/2021     1/4/2022

Note: From and To are reserved words and should not be used as field names.

 

[Capitala]

you could do something along these lines:

Sub OneYear(dteStart As Date)

    Dim i As Integer
    Dim dteS As Date
    dteS = dteStart

    For i = 1 To 12
        Debug.Print i, dteS, DateAdd("m", 1, dteS) - 1
        dteS = DateAdd("m", 1, dteS)

    Next i

End Sub

 1            1/5/2021      2/4/2021
2            2/5/2021      3/4/2021
3            3/5/2021      4/4/2021
4            4/5/2021      5/4/2021
5            5/5/2021      6/4/2021
6            6/5/2021      7/4/2021
7            7/5/2021      8/4/2021
8            8/5/2021      9/4/2021
9            9/5/2021      10/4/2021
10           10/5/2021     11/4/2021
11           11/5/2021     12/4/2021
12           12/5/2021     1/4/2022

Thanks a mile. I'll try it.

 

[Capitala]

Thanks a mile. I'll try it.

It's almost ready. The only issue now is the last period as it could be a fraction of a month. i.e. the contract expires on 16-12-2020 for example.

 

[arnelgp]

period 1: from 5-12-2020 to 4-1-2021, period 2: from 5-1-2021 to 4-2-2021

using query alone + additional table, you can accomplished the same.
see query1.