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

Posted on 2007-08-01
Last Modified: 2013-11-27
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?
Question by:howcheat
    LVL 44

    Assisted Solution

    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
    LVL 33

    Expert Comment

    by:Mike Eghtebas
    SQL... GRayL... no chance for me to score then.
    LVL 15

    Accepted Solution

    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()
    ' PURPOSE:
    '   Gives a list of the last 12 months in a combo box or list box.
    ' USAGE:
    '   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
        Case LB_INITIALIZE
            ' 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

        Case LB_GETVALUE
            ' Are you requesting the first row?
            DispDate = DateAdd("m", (row * -1), Date)
            MonthList = DispDate
        Case LB_GETFORMAT
            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
    LVL 44

    Expert Comment

    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?

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now