Extraxt months

[arcootje]

Is the following possible:

START DATE: 03-01-2005
END DATE: 16-02-2006

Is it possible to get the following output:
START DATE END DATE
03-01-05 31-01-05
01-02-05 28-02-05
01-03-05 31-03-05
01-04-05 30-04-05
01-05-05 31-05-05
01-06-05 30-06-05
01-07-05 etc...

Thanx in advance ...

 

[SydneyGeek]

Hi arcootje,

What do you need to do? It is possible to generate a list of dates like this, but whether or not you need to will depend on what outcome is required.

Also, if you do need to create this list, where does it need to end up?
In a table as a bunch of records
In the rowsource of a listbox or combo box
somewhere else...

Denis

 

[arcootje]

the output must come in a new table ...

orginal table headers:
CODE
START
END
VALUE

output contains the same headers, but the date must be split

thanx

 

[SydneyGeek]

OK, here is one way to do it.

If you have Access 2000 or higher, go into any code module then Tools > References and check the box next to Microsoft DAO 3.6 Object Library.

You will need:
1. Table (I called mine tblDates) with fields StartDate and EndDate. You can have other fields but they are not required for the code.

2. Unbound form with the following:
a. textbox called txtStart
b. textbox called txtEnd
c. Listbox called lstDates, which displays at least StartDate and EndDate from tblDates
d. Button

3. The code for the button's Click event is this --

    Dim dbs As DAO.database
    Dim rst As DAO.Recordset
    Dim dFirst As Date
    Dim dLast As Date
    Dim dRecStart As Date
    
    dFirst = Me.txtStart
    dLast = Me.txtEnd
    dRecStart = Me.txtStart
    
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("tblDates")
    With rst
        Do While dRecStart < dLast
            .AddNew
            !startdate = dRecStart
            If dLast < DateSerial(Year(dRecStart), Month(dRecStart) + 1, 0) Then
                !enddate = dLast
            Else
                !enddate = DateSerial(Year(dRecStart), Month(dRecStart) + 1, 0)
            End If
            .Update
            dRecStart = DateSerial(Year(dRecStart), Month(dRecStart) + 1, 1)
        Loop
    End With
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Me.lstDates.Requery

It does the following:
1. Creates the records
2. Requeries the listbox so you see the result.
Note: the code does NOT check that the records already exist before creating new ones. You will need to adjust table, field and control names to suit.

Denis