?
Solved

Date Range Option

Posted on 2005-04-30
7
Medium Priority
?
216 Views
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:
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.
0
Comment
Question by:billcute
7 Comments
 
LVL 11

Expert Comment

by:Craig Yellick
ID: 13900672
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
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13900677
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?
0
 
LVL 17

Expert Comment

by:Arji
ID: 13900696
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)



0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 17

Expert Comment

by:Arji
ID: 13900706
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 & "#"
0
 
LVL 17

Expert Comment

by:Arji
ID: 13902089
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 & "#"
0
 
LVL 5

Accepted Solution

by:
BevinManian earned 2000 total points
ID: 13925689
Bill,
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
Else
    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!!"
        Else
            'open the report
             docmd.openreport "blah blah blah"
endif

HTH
Bev
0
 
LVL 4

Author Comment

by:billcute
ID: 13930962
Bev,
Your explanation and codes are quite detailed and self explanatory. Thanks for the efforts

Regards
Bill
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

862 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