• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 826
  • Last Modified:

Combo box with Months to retrieve monthly data between first day and last day of month

I would like to create a combo box that will list the dates Jan 2006 - Dec 2007 and retrieve the data for the specific month from my query. For example, a user would click on Jan 2006 and the query would retreive all records from 1-31 Jan 2006. I have created a combo box with two fields, the first one is one through 12 (for each month of the year) and the second field in the corresponding month spelled out. I hid the first column and made it the bound column. I need to somehow associate this with the wrk_date field on my query. So ultimately, I want the user to be able to click a specific month on the combobox and retrieve all the applicable monthly transactions. And what do you do when you change years? Would you increase the size of the combo box and label Jan 2007 as 13, Feb 2007 as 14 and so on?
  • 2
2 Solutions
Have another combobox for Years.  

assumming the boxes are named cboMonth and cboYear

Select * from myTable Where Format(fldDate,"yyyym") = Forms!myForm!cboYear & Forms!myForm!cboMonth
Mike EghtebasDatabase and Application DeveloperCommented:
SQL... GRayL... no chance for me to score then.
I use the function below to fill a combobox with a list of the last 12 months - add a combobox and set its RowSourceType to "MonthList" and the Tag property to your required format (it defaults to "mmm yyyy").

When a month is picked, two hidden text boxes (txtMonthStart and txMonthEnd) are filled with the Start and end dates of the month, and these are referred to in the query, rather than the combo

Function MonthList(C As Control, id As Long, row As Long, col As Long, Code As Integer) As Variant

' FUNCTION: Monthlist()
'   Gives a list of the last 12 months in a combo box or list box.
'   1. Create a combo box or list box that displays the data you
'      want.
'   2. Change the RowSourceType property from "Table/Query" to
'      "Monthlist."
'   3. Put the required date format in the control's tag property
'      Otherwise use the default specified in the initialise section
    Static DISPLAYID As Long
    Static DISPLAYCOL As Integer
    Static DisplayText As String
    Static DisplayFormat As String

    Dim DispDate As Variant

    On Error GoTo Err_Monthlist

    Select Case Code
        ' See if the function is already in use.
        If DISPLAYID <> 0 Then
            MsgBox "Monthlist is already in use by another control!"
            MonthList = False
            Exit Function

        End If

        If Not IsNull(C.Tag) Then
            DisplayFormat = C.Tag
            DisplayFormat = "mmm - yyyy"
        End If

        ' Parse the display format from the Tag
        ' property.
        ' Record and return the ID for this function.
        DISPLAYID = Timer
        MonthList = DISPLAYID

    Case LB_OPEN
        MonthList = DISPLAYID

        ' Return the number of rows in the list.
        MonthList = 12

        ' Return the number of fields (columns) in the list.
        MonthList = 1

        MonthList = -1

        ' Are you requesting the first row?
        DispDate = DateAdd("m", (row * -1), Date)
        MonthList = DispDate
        MonthList = DisplayFormat

    Case LB_END
        DISPLAYID = 0

    End Select

    Exit Function


Beep:     MsgBox Error$, vbCritical + vbOKOnly, "Monthlist"
    MonthList = False
    Resume Bye_Monthlist
End Function


In the On Change event of the combo I have the following:

Private Sub cmbMonth_Change()

On Error GoTo HandleErr

Me.txtMonthStart = CDate("01 " & Me.cmbMonth.Value)         'Start date of selected month
Me.txtMonthEnd = EndOfMonth(Me.txtMonthStart)               'end date of selected month

  Exit Sub

' Error handling block added by VBA Code Commenter and Error Handler Add-In. DO NOT EDIT this block of code.
' Automatic error handler last updated at 04 August 2005 11:26:03
  Select Case Err.Number
    Case Else
       MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Form_frmReportSelector.cmbMonth_Change"
  End Select
  Resume ExitHere
' End Error handling block.
End Sub

Function EndOfMonth(D As Variant) As Variant
' Returns the date representing the last day of the current month.
' Arguments:
' D            = Date
  EndOfMonth = DateSerial(Year(D), Month(D) + 1, 0)
End Function
Thanks, just curious, did you try my approach?  You seem to prefer two extra hidden textboxes, two functions and a sub with a mile of code over an additional combobox and no functions or subs?
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now