Date Range Option

Posted on 2005-04-30
Last Modified: 2008-03-06
Is it possible to give my DB Users the Option of selecting the Year / Quarter in one form; and another "Option" whereby Users can type the dates directly into the Date Range Form such that the first thing Users will see is a "dialog box" which we will present the two option to them. After the user selects an option, the corresponding form will be loaded as follows:
   Search Criteria:
Select the Year and the Quarter you wish to print.
Option 2
Type in the "Begin Dates" and Ending Dates" yourself
If a Users selects Option 1, then a form showing both the Year and Quarter field with the Preview Button will appear"
If a users selects Option 2, he sees a different form with two fields "Begin Date and Ending Date with the Preview Button.
This way, users will have the flexibility to choose between the two options. Any help will be appreciated.
Question by:billcute
    LVL 11

    Expert Comment

    This is a common user interface challenge. Here's my approach: The rest of the form or report looks for a begin date and end date, and that's all. The form always knows about a single begin date and a single end date. So the challenge is how to make it easy for the user to set these dates.

    My solution is to offer a series of radio option buttons (the kind where only one is selected at a time). They are arrange like so, you can eliminate any that aren't relevent.  The square brackets indicate a combo box control.

      ( * )  All dates
      (    )  Single year [       ]
      (    )  Year [       ]  and quarter  [ Q1 ]
      (    )  Year [       ]  and month [ January ]
      (    )  Range from [  date1   ]  through [  date2  ]

    In code behind the form, you watch the AfterUpdate event for the frame that contains these controls and set the form's hidden begin and end date controls accordingly.  "All Dates" means you set the begin date to cdate(-99999) and cdate(99999) which will include all dates.

    A fancier implementation enables/disables the combo boxes and text boxes such that only the ones relevant to the selection are enabled.

    -- Craig Yellick
    LVL 26

    Expert Comment

    I'd use a combo to select a choice.
    Have a value list "Date Range", "Annual", "Quarterly" then use a CASE statement to idenify what to do:

    Dim mySQL as String

    SELECT CASE myCombo
    CASE "Date Range"
    mySQL = "SELECT blah blah FROM blah blah WHERE (((blah blah) BETWEEN [First Date] AND [Last Date]));)

    CASE "Annual"
    mySQL = "Blah blah"
    CASE "Quarterly"
    mySQL = "Blah blah"
    Msgbox "You must select a report type"


    LVL 17

    Expert Comment

    This is definitely possible depending on how skilled you are at coding.  I would suggest using the two Calender Controls for picking your begining and ending dates. That way users can be very precise at selecting a time frame.  They can also select a quarter, two quarters. the whole year, a previous year, etc. You can then open a report or form based on what your users select:

    DoCmd.OpenReport "YourReportName", acPreview, , "reportdatefield >=#" & [BeginningDateControl].Value _
    & "# reportdatefield <=#" & _[EndingDateControl].Value & "#"

    for a form

    DoCmd.OpenForm "YourFormtName", , , "reportdatefield >=#" & [BeginningDateControl].Value _
    & "# reportdatefield <=#" & _[EndingDateControl].Value & "#"

    This assumes your report or form has the data you are looking for as the underlying recordset and the underlying recordset has some sort of date in to be able to search by(reportdatefield)

    LVL 17

    Expert Comment

    Oops!  I somehow type an underscore in the code before the endingdatecontrol.  Here is the right way:

    DoCmd.OpenReport "YourReportName", acPreview, , "reportdatefield >=#" & [BeginningDateControl].Value _
    & "# reportdatefield <=#" & [EndingDateControl].Value & "#"

    for a form

    DoCmd.OpenForm "YourFormtName", , , "reportdatefield >=#" & [BeginningDateControl].Value _
    & "# reportdatefield <=#" & [EndingDateControl].Value & "#"
    LVL 17

    Expert Comment

    Dang it! another mistake!!! hope i get it right this time :-)

    DoCmd.OpenReport "YourReportName", acPreview, , "reportdatefield >=#" & [BeginningDateControl].Value _
    & "# AND reportdatefield <=#" & [EndingDateControl].Value & "#"

    for a form

    DoCmd.OpenForm "YourFormtName", , , "reportdatefield >=#" & [BeginningDateControl].Value _
    & "# AND reportdatefield <=#" & [EndingDateControl].Value & "#"
    LVL 5

    Accepted Solution

    The below steps, though a little tedious, provides you the solution - with a little cosmetic appearence to it too!!!

    On your dialog form, firstly get a Option Group done (as mentioned by CraigYellick), with the following options
    * Quarter
    * Date Range

    In the "After Update" property of the option group, paste the below code

    If Me.ReportToPrint = 1 Then
        Me.QTR.Visible = True
        Me.QUARTER_Label.Visible = True
        Me.input_year.Visible = True
        Me.Label10.Visible = True
        Me.QTR = ""
        Me.input_year = ""
        Me.BeginDate.Visible = False
        Me.BeginDate_Label.Visible = False
        Me.EndDate.Visible = False
        Me.EndDate_Label.Visible = False
        Me.BeginDate.Visible = True
        Me.BeginDate_Label.Visible = True
        Me.EndDate.Visible = True
        Me.EndDate_Label.Visible = True
        Me.BeginDate = ""
        Me.EndDate = ""
        Me.QTR.Visible = False
        Me.QUARTER_Label.Visible = False
        Me.input_year.Visible = False
        Me.Label10.Visible = False
    End If

    This hides or unhides the other input boxes on the form based on option you choose.  The input boxes are in pairs...
    One set for the first option, ie, YEAR and QUARTER
    Second set for the Date Range option, ie, BeginDate and EndDate

    Note that your report could use the BeginDate & EndDate fields to acutally run the query/report for either of the options.  This way, your query can stay intact without any change.

    Another aspect to add to the "cosmetic" part of your application would be not to print any report if no records are retrieved by the query.

    If DCount("[keyfld]", "rptQuery") = 0 Or IsNull(DCount("[keyfld]", "rptQuery")) Then
                MsgBox "Nothing to Print!!"
                'open the report
                 docmd.openreport "blah blah blah"

    LVL 4

    Author Comment

    Your explanation and codes are quite detailed and self explanatory. Thanks for the efforts


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

    760 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

    7 Experts available now in Live!

    Get 1:1 Help Now