Date Calculation (Time Frame) in Query

[Proaccesspro]

I have several reports that are date driven. (There is a Date field called date received in the DB) I need to offer the user a time frame for each report. For example, I have a report named by employee. I want to offer the user the opportunity to run the report from say 12/31/07 - 1/31/08. Can I do something like this in the date received field of the query?

>=[Enter FROM Date]; <=[Enter TO Date].

Also, How can I create a mask so the user has to enter the date MM/DD/YYYY?

[MindBender77]

I have several reports that are date driven. (There is a Date field called date received in the DB) I need to offer the user a time frame for each report. For example, I have a report named by employee. I want to offer the user the opportunity to run the report from say 12/31/07 - 1/31/08. Can I do something like this in the date received field of the query?

>=[Enter FROM Date]; <=[Enter TO Date].

Also, How can I create a mask so the user has to enter the date MM/DD/YYYY?

The easiest way might be to have a small form ie. popup or modal with 2 text boxes. Make one DateFrom and the other DateTo. On the OnExit event of the DateTo textbox run the report. Using code like this:

DoCmd.OpenReport ("YourReport", acpreview)
 

In the query that produces the report, under the criteria for Date:
This makes the input from the Form the criteria in the query.
>=Forms![FormName1]![DateFrom] and <=Forms![FormName1![DateTo]

Or
between Forms![FormName1]![DateFrom] and Forms![FormName1![DateTo]

As for the input mask try: In the properties of the textbox, under the data tab
99/99/9999;0;_

Hope This Helps,
JS

[Proaccesspro]

The easiest way might be to have a small form ie. popup or modal with 2 text boxes. Make one DateFrom and the other DateTo. On the OnExit event of the DateTo textbox run the report. Using code like this:

DoCmd.OpenReport ("YourReport", acpreview)
 

In the query that produces the report, under the criteria for Date:
This makes the input from the Form the criteria in the query.
>=Forms![FormName1]![DateFrom] and <=Forms![FormName1![DateTo]

Or
between Forms![FormName1]![DateFrom] and Forms![FormName1![DateTo]

As for the input mask try: In the properties of the textbox, under the data tab
99/99/9999;0;_

Hope This Helps,
JS

JS,

One hang-up. I have a window that allows the user to select a report from a list. How will I know which reoprt the user selected when this code is executed?

DoCmd.OpenReport ("YourReport", acpreview)

I'm guessing I would have to implement some kind of Case statement?

[jaxjagfan]

I have several reports that are date driven. (There is a Date field called date received in the DB) I need to offer the user a time frame for each report. For example, I have a report named by employee. I want to offer the user the opportunity to run the report from say 12/31/07 - 1/31/08. Can I do something like this in the date received field of the query?

>=[Enter FROM Date]; <=[Enter TO Date].

Also, How can I create a mask so the user has to enter the date MM/DD/YYYY?

I have a form that a user enters a start date and an end date into 2 text boxes on the form. There is a button to open the report.
There is a query that drives the report.
In the Criteria of the my date field I have this:

Between [Forms]![frmReports]![txtStart] And [Forms]![frmReports]![txtEnd]

My process is actually far more involved with reports being chosen from a listbox, dates being entered, and possible values to filter the reports picked from a multiselect list .... but let's keep it simple to begin with.

[Proaccesspro]

I have a form that a user enters a start date and an end date into 2 text boxes on the form. There is a button to open the report.
There is a query that drives the report.
In the Criteria of the my date field I have this:

Between [Forms]![frmReports]![txtStart] And [Forms]![frmReports]![txtEnd]

My process is actually far more involved with reports being chosen from a listbox, dates being entered, and possible values to filter the reports picked from a multiselect list .... but let's keep it simple to begin with.

Mine sounds similar....I have a list of reports available to the user...once they select a report they would click on the Print Report button. A pop-up window then appears asking them to input a date range...once input, the report is sent to the printer. My problem is, how do I know which report was selected by the user?

[MindBender77]

Mine sounds similar....I have a list of reports available to the user...once they select a report they would click on the Print Report button. A pop-up window then appears asking them to input a date range...once input, the report is sent to the printer. My problem is, how do I know which report was selected by the user?

In theory, you could make a print report button for each report; make them all invisible until the user selects a report. After a selection is made, only that print button is visible.

if Listbox1.selected = "Report1" then
Button1.visible=true
button2.visible = false
etc. etc.
end if
 

[Proaccesspro]

In theory, you could make a print report button for each report; make them all invisible until the user selects a report. After a selection is made, only that print button is visible.

if Listbox1.selected = "Report1" then
Button1.visible=true
button2.visible = false
etc. etc.
end if
 

I tried to implement but getting an error. Here is my code:

If ReportList.ItemSelected = "By Age (Open Appelas)" Then
PrintBtn1.Visible = True
PrintBtn1.Visible = flase
PrintBtn1.Visible = False
PrintBtn1.Visible = False
PrintBtn1.Visible = False
PrintBtn1.Visible = False
PrintBtn1.Visible = False
PrintBtn1.Visible = False
End If

I have this code in a listbox named reportlist. I have it under the ON CLICK section. It stops at item selected and says "method or data member not found"

[jaxjagfan]

I tried to implement but getting an error. Here is my code:

If ReportList.ItemSelected = "By Age (Open Appelas)" Then
PrintBtn1.Visible = True
PrintBtn1.Visible = flase
PrintBtn1.Visible = False
PrintBtn1.Visible = False
PrintBtn1.Visible = False
PrintBtn1.Visible = False
PrintBtn1.Visible = False
PrintBtn1.Visible = False
End If

I have this code in a listbox named reportlist. I have it under the ON CLICK section. It stops at item selected and says "method or data member not found"

[code]
If Me.lstChannelReport.ItemsSelected.Count = 0 Then
MsgBox "No Report Selected!
Me.lstChannelReport.SetFocus
Else
DoCmd.OpenReport Me.lstChannelReport.Column(0), acViewPreview
End If
{/CODE]

lstChannelReport is a listbox with 3 columns. The 1st column contains the Access name for the report and its width is set to 0, 2nd column is a Descriptive Name the user picks from ("Quarterly Sales Report" where the actual report is named rptQtrSales). The 3rd column (width is set to 0) is a report description. Its value displays in an unbound textbox after the user selects a report.

You can attach this code to a single cmdbutton - no need to do all the hide/show.

I have the 2 date textboxes for user to enter dates.

I saw a commercial app with a similar layout and made my own version in Access. I'm including a screenshot of the layout - maybe it will give you some ideas.

[Proaccesspro]

[code]
If Me.lstChannelReport.ItemsSelected.Count = 0 Then
MsgBox "No Report Selected!
Me.lstChannelReport.SetFocus
Else
DoCmd.OpenReport Me.lstChannelReport.Column(0), acViewPreview
End If
{/CODE]

lstChannelReport is a listbox with 3 columns. The 1st column contains the Access name for the report and its width is set to 0, 2nd column is a Descriptive Name the user picks from ("Quarterly Sales Report" where the actual report is named rptQtrSales). The 3rd column (width is set to 0) is a report description. Its value displays in an unbound textbox after the user selects a report.

You can attach this code to a single cmdbutton - no need to do all the hide/show.

I have the 2 date textboxes for user to enter dates.

I saw a commercial app with a similar layout and made my own version in Access. I'm including a screenshot of the layout - maybe it will give you some ideas.

It all works GREAT, except I am having a little difficulty with the form I created to capture the date range.

I have the code in the AFTER UPDATE section. I have the properties set to POPUP and MODAL. Once something is entered in both boxes, nothing happens unless I press the tab key or click somewhere else on the screen. Can I make it so that once both fields aer populated, the program continues automatically? Also, the form does not disappear automatically once the data is captured, I have to close it manually.