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

Posted on 2007-08-01
Medium Priority
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
  • 2
LVL 44

Assisted Solution

GRayL earned 400 total points
ID: 19612771
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 34

Expert Comment

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

Accepted Solution

cquinn earned 1600 total points
ID: 19615513
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
LVL 44

Expert Comment

ID: 19702880
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

850 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