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.
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.
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?
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].Val ue _
& "# reportdatefield <=#" & _[EndingDateControl].Value & "#"
for a form
DoCmd.OpenForm "YourFormtName", , , "reportdatefield >=#" & [BeginningDateControl].Val ue _
& "# 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)
DoCmd.OpenReport "YourReportName", acPreview, , "reportdatefield >=#" & [BeginningDateControl].Val
& "# reportdatefield <=#" & _[EndingDateControl].Value
for a form
DoCmd.OpenForm "YourFormtName", , , "reportdatefield >=#" & [BeginningDateControl].Val
& "# 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].Val ue _
& "# reportdatefield <=#" & [EndingDateControl].Value & "#"
for a form
DoCmd.OpenForm "YourFormtName", , , "reportdatefield >=#" & [BeginningDateControl].Val ue _
& "# reportdatefield <=#" & [EndingDateControl].Value & "#"
DoCmd.OpenReport "YourReportName", acPreview, , "reportdatefield >=#" & [BeginningDateControl].Val
& "# reportdatefield <=#" & [EndingDateControl].Value & "#"
for a form
DoCmd.OpenForm "YourFormtName", , , "reportdatefield >=#" & [BeginningDateControl].Val
& "# reportdatefield <=#" & [EndingDateControl].Value & "#"
Dang it! another mistake!!! hope i get it right this time :-)
DoCmd.OpenReport "YourReportName", acPreview, , "reportdatefield >=#" & [BeginningDateControl].Val ue _
& "# AND reportdatefield <=#" & [EndingDateControl].Value & "#"
for a form
DoCmd.OpenForm "YourFormtName", , , "reportdatefield >=#" & [BeginningDateControl].Val ue _
& "# AND reportdatefield <=#" & [EndingDateControl].Value & "#"
DoCmd.OpenReport "YourReportName", acPreview, , "reportdatefield >=#" & [BeginningDateControl].Val
& "# AND reportdatefield <=#" & [EndingDateControl].Value & "#"
for a form
DoCmd.OpenForm "YourFormtName", , , "reportdatefield >=#" & [BeginningDateControl].Val
& "# AND reportdatefield <=#" & [EndingDateControl].Value & "#"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Bev,
Your explanation and codes are quite detailed and self explanatory. Thanks for the efforts
Regards
Bill
Your explanation and codes are quite detailed and self explanatory. Thanks for the efforts
Regards
Bill
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