Listing the last and 15th day of every month in a combo box.

I have issues getting this code work. I have been able to link it to a fill box and generate the dates but only either the 15th of each month is listed  or the last day of the month. Below is the code I have been working on. any help is very much appreciated.

Function ListMondays(fld As Control, id As Variant, _
    row As Variant, col As Variant, code As Variant) _
     As Variant
    Dim intOffset As Integer
    Dim temp As Integer

        temp = 0

    Select Case code
        Case acLBInitialize            ' Initialize.
            ListMondays = True
        Case acLBOpen                    ' Open.
            ListMondays = Timer        ' Unique ID.
        Case acLBGetRowCount            ' Get rows.
            ListMondays = 10
        Case acLBGetColumnCount    ' Get columns.
            ListMondays = 1
        Case acLBGetColumnWidth    ' Get column width.
            ListMondays = -1            ' Use default width.
        Case acLBGetValue                ' Get the data.
            'ListMondays = DateSerial(Year(Date), Month(Date) + 1 + row, 1) - 1
        'Case acLBGetFormat
            'ListMondays = "mm/dd/yyyy"
            'intOffset = Abs((9 - Weekday(Now)) Mod 7)
           Select Case temp
           Case Abs((temp) Mod 2) = 0
                ListMondays = DateSerial(Year(Date), Month(Date) + temp, 1) - 1
                'row = row + 1
                'ListMondays = DateSerial(Year(Date), Month(Date) + temp, 1) + 14
           Case Abs((temp) Mod 2) = 1
               ListMondays = DateSerial(Year(Date), Month(Date) + row, 1) + 14
          End Select
            temp = temp + 1
    End Select
Who is Participating?
Gustav BrockCIOCommented:
Jeff and Ray, this is a callback function "selfgenerating" the values. No tables needed.

Here is how to obtain the list:
Public Function ListFifteenthAndLastDayOfMonthInYear( _
  ctl As Control, _
  lngId As Long, _
  lngRow As Long, _
  lngCol As Long, _
  intCode As Integer) As Variant
  ' 2011-04-27. Cactus Data ApS, CPH.

  ' Count of months.
  Const cintMonths    As Integer = 12
  ' Fifteenth day of month.
  Const cintFifteenth As Integer = 15
  ' Last day of month.
  Const cintLastDay   As Integer = 0
  ' Format for ListBox.
  Const cstrFormat    As String = "yyyy\-mm\-dd"
  Static intYear      As Integer
  Static strFormat    As String
  Dim varValue        As Variant
  Select Case intCode
    Case acLBInitialize
      intYear = Year(Date)
      If ctl.ControlType = acComboBox Then
        ' Use format of control.
        strFormat = ctl.Format
        ' Use predefined format.
        strFormat = cstrFormat
      End If
      varValue = True             ' True to initialize.
    Case acLBOpen
      varValue = Timer            ' Autogenerated unique ID.
    Case acLBGetRowCount          ' Get rows.
      varValue = cintMonths * 2   ' Set number of rows.
    Case acLBGetColumnCount       ' Get columns.
      varValue = 1                ' Set number of columns.
    Case acLBGetColumnWidth       ' Get column width.
      varValue = -1               ' Use default width.
    Case acLBGetValue             ' Get the data.
      If lngRow Mod 2 = 0 Then
        ' Get the fifteenth day of this month.
        varValue = DateSerial(intYear, Int(lngRow / 2) + 1, cintFifteenth)
        ' Get the last day of this month.
        varValue = DateSerial(intYear, -Int(-lngRow / 2) + 1, cintLastDay)
      End If
    Case acLBGetFormat            ' Format the data.
      varValue = strFormat        ' Apply format of the generated strings.
    Case acLBEnd
      ' Do something when the form with the control closes or
      ' the control is requeried.
  End Select
  ' Return Value.
  ListFifteenthAndLastDayOfMonthInYear = varValue

End Function

Open in new window

If you want this for say a two year period, you need a small table Nums with an integer field Num containing the values 0-23.  Then run this query:

SELECT DateSerial(Year(Date), Month(Date) + Num, 15) as MidMonthDate,
DateSerial(Year(Date), Month(Date)+Num+1,0) As EndMonthDate FROM Nums;
If you like what you see, then make that query the Control Source of your Combo Box.
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Sorry, that should be Row Source, not Control Source.
Jeffrey CoachmanMIS LiasonCommented:
Since you did not post any source data, I am not sure how any dates are represented in your DB...

But if you have a table (or query) listing all sequential dates, then something roughly like this will list all the 15ths and all the last days of each month, in a single column, in a combobox:

SELECT DISTINCT DateSerial(Year([YourDateField]),Month([YourDateField])+1,0) AS 15ofMonthOrEOM
FROM YourTable
UNION SELECT IIf(Day([YourDateField])=15,[YourDateField],Null)
FROM YourTable
WHERE (((IIf(Day([YourDateField])=15,[YourDateField],Null)) Is Not Null));

This can probably be simplified, ..but it works as-is...

Sample attached

SheogorathAuthor Commented:
This is great! Thank you very much :)  ComboBox
Gustav:  Hi, I appreciate what code can do - I just prefer to minimize the use of code in db development.  I guess I'm a bit quirky that way ;-)
SheogorathAuthor Commented:
Well I learned three approaches to achieve this. Thanks again to all.
Gustav BrockCIOCommented:
You are welcome!

Callback functions is one of the gems of Access. Perfect and fast for tasks like this.

Jeffrey CoachmanMIS LiasonCommented:
<Jeff and Ray, this is a callback function "selfgenerating" the values. No tables needed.>

As always...
    "Everyday is a school day"


I still say a simple table with 24 rows and a small query is better than 60 lines of code in terms of maintenance - in a DB environment.
Gustav BrockCIOCommented:
But Ray, there is no maintenance for this callback function.
And a simple table wouldn't do it; the last day of February changes between 28 and 29.

Gustav:  This is just a case of individual preference.  BTW, my approach handles leap years.  By maintenance I thinking in four years time after the database designer has moved on the boss decides he wants it on the 14th and second last day of every month - I just change two values in the query.  
Gustav BrockCIOCommented:
> I just change two values in the query.  

And I just change one!

 ' Fifteenth day of month minus one.
  Const cintFifteenth As Integer = 14

You need more than that!  - you missed the second-last day of the month.  
Gustav BrockCIOCommented:
OK, but I guess you have figured out how:

  ' Last day of month minus one.
  Const cintLastDay   As Integer = -1

Bingo!  Next thread.
SheogorathAuthor Commented:
Haha you guys are awesome :D
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.