CODEWITHSUNDEEP
vbams-access
dames
dames

Reputation: 1481

Listing Months and number of days in each month using start and end date

Hi I am having a major challenge in MS Access VBA, I have a start Date and End date, and would like to list the months, for example: start_date ="2016-09" and_date="2016-11" I want the result to be:

Month     Days
September - 30
October - 31 
November - 30

Im slightly new to VBA all I am getting is doing the datediff() calculations Me.Text365.Value = DateDiff("d", Me.start_date, Me.end_date)which gives me to total days between the month but no break down

Upvotes: 0

Views: 2158

Answers (2)

Gustav
Gustav

Reputation: 55806

You would use a callback function for this - one of the hidden gems of Access:

' Callback function to list ultimo dates of each month for a count of years.
'
' Typical settings for combobox or listbox:
'   ControlSource:  Bound or unbound
'   RowSource:      Leave empty
'   RowSourceType:  ListUltimoMonths
'   BoundColumn:    1
'   LimitToList:    Yes
'   Format:         Valid format for date values
'   ColumnCount:    1
'
' 2014-09-24. Cactus Data ApS, CPH.
'
Public Function ListUltimoMonths( _
    ctl As Control, _
    Id As Long, _
    Row As Long, _
    Column As Long, _
    Code As Integer) _
    As Variant

    ' Count of months in a year.
    Const MonthCount    As Integer = 12
    ' Count of years to list.
    Const Years         As Integer = 3

    Static Start        As Date
    Static Format       As String
    Static Rows         As Integer

    Dim Value           As Variant

    Select Case Code
        Case acLBInitialize
            Start = Date            ' Date of first month to list.
            Rows = MonthCount * Years
            Format = ctl.Format
            Value = True            ' True to initialize.
        Case acLBOpen
            Value = Timer           ' Autogenerated unique ID.
        Case acLBGetRowCount        ' Get rows.
            Value = Rows            ' Set number of rows.
        Case acLBGetColumnCount     ' Get columns.
            Value = 1               ' Set number of columns.
        Case acLBGetColumnWidth     ' Get column width.
            Value = -1              ' Use default width.
        Case acLBGetValue           ' Get the data for each row.
            Value = DateSerial(Year(Start), Month(Start) + Row + 1, 0)
        Case acLBGetFormat          ' Format the data.
            Value = Format          ' Use format of control.
        Case acLBEnd
            ' Do something when form with listbox closes or
            ' listbox is requeried.
    End Select

    ' Return Value.
    ListUltimoMonths = Value

End Function

Follow the in-line instructions. Then set the Format of the control to:

mmmm - dd

and you are done:

enter image description here

Upvotes: 0

user6432984
user6432984

Reputation:

To get the last day of the month using DateSerial: Year of date, month of date + 1, and 0 for the day value:

DateSerial(Year(mydate),Month(mydate) + 1,0)

enter image description here

Upvotes: 2

Related Questions