Avatar of Boris
Boris
Flag for Bosnia and Herzegovina asked on

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
Microsoft Access

Avatar of undefined
Last Comment
Boris

8/22/2022 - Mon
PatHartman

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

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

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

Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Boris

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Gustav Brock

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

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
PatHartman

Boris,
Did you try the expression I posted?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Boris

ASKER
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
PatHartman

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

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Boris

ASKER
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
Gustav Brock

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

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Gustav Brock

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

ASKER
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
Gustav Brock

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?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Boris

ASKER
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
PatHartman

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

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Boris

ASKER
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!
Gustav Brock

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

ASKER
Solution has provided the required results
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy