Link to home
Start Free TrialLog in
Avatar of billcute
billcute

asked on

Date Range Option

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:
Option1
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.
Avatar of Craig Yellick
Craig Yellick
Flag of United States of America image

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
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"
CASE ELSE
Msgbox "You must select a report type"

END SELECT

Idea?
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)



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 & "#"
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 & "#"
ASKER CERTIFIED SOLUTION
Avatar of BevinManian
BevinManian

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of billcute
billcute

ASKER

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

Regards
Bill