How to create date range within existing date range?

Where StartDate >= Forms!yourform!txtStartDate AND EndDate <= Forms!yourform!txtEndDate

>>
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.

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

Select allOpen in new window

That was a very long way to go - and probably slow compared to my code which will run in a split second.

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

Select allOpen in new window

See the attached demo.
Gustav_code.accdb

Boris,
Did you try the expression I posted?

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

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.

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

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

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.

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.

My first code did exactly that: Primo and ultimo for each month of the interval.

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

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?

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

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.

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.

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!

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.

Solution has provided the required results