How to create date range within existing date range?

I have a table with StartDate and EndDate field. (Period of late payment e.g. StartDate: 10/01/2017 and End_date: 12/31/2017)
So, in my example StartDate (10/01/2017) always has to be first date in the range and EndDate (12/31/2017) always has to be last date in the range.
My problem is how to create a query with a range of dates when new date take a place as a new start date within entered time period? (e.g. Payments from 11/01/2017 and 12/30/2017). So, payment date  has to “breakdown” mentioned date range.
The result should look like this:

StartDate        EndDate
10/01/201710/31/2017
11/01/2017  12/29/2017
12/30/2017 12/31/2017
BorisAsked:
Who is Participating?
 
BorisAuthor Commented:
Thanks Gustav for your effort. After some time spent I could not get the desired results with your code.
I took different (much longer) approach and got desired results.
-First, I created data macro on table (tblIntrest) field Enddate (Edate)- iif StartDate (Sdate) is not null to add day before Sdate.
-Second, I created query (query2) from table an created new field "OLD ID"  ([ID]-1 AS OLDID).
-Then I created new query (query3) with ''FROM tblIntrest LEFT JOIN Query2 ON tblIntrest.ID = Query2.OLDID". So I got "breakeddown" EndDate (Edate) from query2.Edate, but with last row empty on EndDate (Edate).
- After that I created new query (qryIntrestFIRST)  (SELECT Max(tblIntrest.Edate) AS MaxOfEdate, tblIntrest.IDclient
FROM tblIntrest GROUP BY tblIntrest.IDclient;) in order to get last date for my empty row.
Finally, created new query with "query3" and "qryIntrestFIRST" with new field "qEdate: IIf([Query2]![Edate] Is Null;[MaxOfEdate];[Query2]![Edate]) .
0
 
PatHartmanCommented:
Where StartDate >= Forms!yourform!txtStartDate AND EndDate <= Forms!yourform!txtEndDate
0
 
Ryan ChongCommented:
>>
StartDate        EndDate
10/01/201710/31/2017
11/01/2017  12/29/2017
12/30/2017 12/31/2017

it depends on the logic how these ranges are being generated but to get the result you wanted, you may try to populate these date ranges into an interim table, so that you can join it with your origin query to get the grouped result.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Download your Guide
 
Gustav BrockCIOCommented:
Use a form, bound to the table, and the RecordsetClone. Then have a button to run this code:

Dim rs As DAO.Recordset

Dim Months As Integer
Dim Item As Integer
Dim Primo As Date

Set rs = Me.RecordsetClone
Months = DateDiff("m", Me!FirstDate.Value, Me!LastDate.Value)   ' Unbound, to enter dates.
Primo = DateSerial(Year(Me!FirstDate.Value), Month(Me!FirstDate.Value), 1) 

For Item = 0 To Months
    rs.AddNew
        rs!StartDate.Value = DateAdd("m", Item, Primo)
        rs!EndDate.Value = DateSerial(Year(Primo), Month(Primo) + 1, 0)
    rs.Update
    Item = Item + 1
Next
rs.Close

Open in new window

1
 
Gustav BrockCIOCommented:
That was a very long way to go - and probably slow compared to my code which will run in a split second.
0
 
Gustav BrockCIOCommented:
You can do like this:

Private Sub Command41_Click()

    Dim rs      As DAO.Recordset

    Dim Days    As Integer
    Dim Item    As Integer
    Dim Primo   As Date
    
    Set rs = Me.RecordsetClone
    Days = DateDiff("d", Me!FirstDate.Value, Me!LastDate.Value)   ' Unbound, to enter dates.
    
    Primo = DateSerial(Year(Me!FirstDate.Value), Month(Me!FirstDate.Value), 1)
    
    Me.Painting = False

    For Item = 0 To Days
        rs.AddNew
            rs!StartDate.Value = DateAdd("d", Item, Primo)
            rs!EndDate.Value = DateAdd("d", Item + 1, Primo)
        rs.Update
    Next
    rs.Close
    
    Me.Requery
    Me.Painting = True

End Sub

Open in new window

See the attached demo.
Gustav_code.accdb
1
 
PatHartmanCommented:
Boris,
Did you try the expression I posted?
0
 
BorisAuthor Commented:
PatHartman,

Thank you for your time!

If I apply your criteria in query with following data on my form;
Start date    End date
01.10.2017  31.12.2017
01.11.2017
30.12.2017

query only returns 01.10.2017 and 31.12.2017, which is not my desired result. Date ranges are not "breakeddown" with payment date.
First entry always (01.10.2017-always first date in range and 31.12.2017-always last date in range) defines date range, other dates, if entered (payment date), are single entry without an end date. Day before payment day is EndDate for the previous period an so on.

Maybe I am missing something.

Thanks once more,

Boris
0
 
PatHartmanCommented:
Where StartDate >= Forms!yourform!txtStartDate AND (EndDate <= Forms!yourform!txtEndDate OR Forms!yourform!txtEndDate Is Null)

Pay attention to the parentheses.  Once you use AND and OR in a compound expression, you almost always need parentheses to ensure that the expression is evaluated as you need it to be.
0
 
Gustav BrockCIOCommented:
You'll have to make up your mind. First you requested:

StartDate      EndDate
10/01/2017  10/31/2017
11/01/2017  12/29/2017
12/30/2017  12/31/2017


and a provided a solution and demo for that.

Then you state:

Start date    End date
01.10.2017  31.12.2017
01.11.2017
30.12.2017
0
 
BorisAuthor Commented:
Gustav,
Sorry If I was not clear enough about my problem.

As I mentioned in my first post, I have fields which determine date range, and, potently, another date which has to “breakdown” range.
So, I have, in my example, date range from 01.10.2017 to 31.12.2017, and two payments dates 01.11.2017 and 30.12.2017.

My expected result is:
StarDate   -  EndDate
01.10.2017 - 31.10.2017
01.11.2017 -29.12.2017
30.12.2017-31.12.2017
0
 
Gustav BrockCIOCommented:
But you also wrote:

I only get monthly range. Payment day is single day, not a date range.

Then I modified the solution to return days.
But if you are back to requesting intervals of months, use my original code that did so.
0
 
BorisAuthor Commented:
Yes, you are right.
But, with provided code (monthly or a day range) I do not get “breakeddown” ranges (that is my primary goal) with payment date where payment day is a new StartDate, and previous EndDate is day before payment date.
0
 
Gustav BrockCIOCommented:
My first code did exactly that: Primo and ultimo for each month of the interval.
0
 
BorisAuthor Commented:
In your first code I see "Primo", but I do not see "ultimo".
If I run your code I get:
01.10.2017-31.10.2017
01.11.2017-30.11.2017
01.12.2017-31.12.2017

I need to get this:
01.10.2017-31.10.2017
01.11.2017-29.12.2017
30.12.2017-31.12.2017
0
 
Gustav BrockCIOCommented:
I see. Ultimo is the last day in month, and that is listed.
But what is the rule or logic behind your mixed month/day list? What would it look like for, say, two and four months?
0
 
BorisAuthor Commented:
Logic is that first and last day in the range can be any day in a year, and that they are continuous, without gaps (They do not need necessarily to start/end on the first/last day in a month). I have to group them by month in order to have number of days in that period and to get debt amount within a mounth(I know how to do this)
If new date is entered on a form (in my case payment date), that day has to become new start date within the range, and day before new start date (payment date) is EndDate for first period.
So, if I take another example with StartDate 15.02.2017 and EndDate 20.04.2017, result would be:
15.02.2017-28.02.2017  100,00 EUR
01.03.2017-31.03.2017  100,00 EUR
01.04.2017-20.04.2017  100,00 EUR
(and this part is not problem for me)

Problem is If I enter new date 05.03.2017, with payment amount 15,00 EUR result should be:
15.02.2017-28.02.2017 100,00 EUR
01.03.2017-04.03.2017 100,00 EUR
05.03.2017-31.03.2017 85,00 EUR
01.04.2017-20.04.2017 85,00 EUR
0
 
PatHartmanCommented:
Boris,
Every time you post, the specs change.    This makes it very difficult for anyone to help you.  I'll leave it to Gus.  He seems to have a handle on this.
0
 
Gustav BrockCIOCommented:
This expert suggested creating a Gigs project.
Yes, this is not easy:

I need to get this:
01.10.2017-31.10.2017
01.11.2017-29.12.2017
30.12.2017-31.12.2017

Now, in your previous post, you are shifting back to months only.
Also, now you have interim dates ... March in your example.

I think I'll have to pass on this. Please have in mind that I (and Pat as well) have a job to do. We are only here for the fun.
It might be better to open a project in Gigs.
0
 
BorisAuthor Commented:
I am sorry guys. Nothing has changed in my posts. I tried to provide another example to explain logic behind dates to Gustav and made a mistake

15.02.2017-28.02.2017 100,00 EUR
01.03.2017-04.03.2017 100,00 EUR
05.03.2017-31.03.2017 85,00 EUR
01.04.2017-20.04.2017 85,00 EUR

It should be like this:
15.02.2017-04.03.2017 100,00 EUR
05.03.2017-20.04.2017 85,00 EUR

I'm a beginner in MS Access and I thought this was not a very complicated question.
Anyway,  thank you for your time!
0
 
Gustav BrockCIOCommented:
It may or may not be complicated, but without an exact description of the business rules, possible solutions will go East and West.

Initially, you had a start and an end date, now it looks more like a running balance with payment(s) at some date(s) between start and end. If so, not only the initial creation of dates but also some kind of interim update must be done. But when and how? We don't know.
0
 
BorisAuthor Commented:
Solution has provided the required results
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here