VBA Code to show all dates between a range

[Karma1004]

Hi everyone,

I'm new here and I'm in need for your expertise.
I have a table called "tbl.DateRange" with the following data:

Number StartDate EndDate
1 05/03/2019 08/03/2019
2 25/02/2019 27/02/2019

I need all dates between the StartDate & EndDate shown per number in a new table. So I need the following:

Number Dates
1 05/03/2019
1 06/03/2019
1 07/03/2019
1 08/03/2019
2 25/02/2019
2 26/02/2019
2 27/02/2019

Can anyone please help me with creating a code for this?
Many Thanks
Karin

 

[ypma]

As a user and not an expert. i would use a form with fields name startdate and EndDate, Then have a query with the based on the table and in the criteria use something like

Between [Forms]![frmReporting]![StartDate] And [Forms]![frmReporting]![EndDate]

Hope this is useful

Ypma

 

[isladogs]

Use a make table query to add the ID and start date fields to a new table.
Next use an append query to add the ID and end date fields to the same two fields.

BTW why duplicate this data?

 

[Karma1004]

Sorry, Maybe I was unclear.
I don't need to duplicate this data but I need to list all calendar dates between the StartDate & the EndDate.

For example: StartDate is 05/03/2019 & EndDate is 09/03/2019
-> The result should be:
05/03/2019
06/03/2019
07/03/2019
08/03/2019
09/03/2019

 

[Uncle Gizmo]

You want to generate a NEW list of dates between two GIVEN dates?

If that's the case then you might find my blog HERE:-

Create Many Records

helpful, in this blog I demonstrate how to create a list of numbers but I'm sure it would be easy to convert it to create dates. If you have any problems with that, post back!

 

[essaytee]

Here's something to get you started:

Sub ProcessDates()

    Dim db As DAO.Database
    Dim rsSource As DAO.Recordset
    Dim rsTarget As DAO.Recordset
        
    Set db = CurrentDb
    
    Dim datCurrent As Date
                
    Set rsSource = db.OpenRecordset("Select * from tblDateRange ORDER BY ID")
    Set rsTarget = db.OpenRecordset("SELECT * FROM tblDateActual")
    
    Do While Not rsSource.EOF
        datCurrent = rsSource("StartDate")
        
        Do While datCurrent >= rsSource("StartDate") And datCurrent <= rsSource("EndDate")
            rsTarget.AddNew
                rsTarget("DateRange") = rsSource("ID")
                rsTarget("ActualDate") = datCurrent
            rsTarget.Update
            datCurrent = datCurrent + 1
        Loop
    
        rsSource.MoveNext
    Loop

End Sub

It's the bare bones. Prior to the first Do While loop, I can't recall whether a rsSource.MoveFirst is required, if it's there it doesn't hurt.

 

[Karma1004]

Thanks essaytee, this works perfectly