By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,478 Members | 1,513 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,478 IT Pros & Developers. It's quick & easy.

Date Calculation (Time Frame) in Query

100+
P: 132
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?
Jan 23 '08 #1
Share this Question
Share on Google+
8 Replies


MindBender77
100+
P: 234
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:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport ("YourReport", acpreview)
  2.  
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
Jan 24 '08 #2

100+
P: 132
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:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport ("YourReport", acpreview)
  2.  
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?
Jan 24 '08 #3

jaxjagfan
Expert 100+
P: 254
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.
Jan 24 '08 #4

100+
P: 132
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?
Jan 24 '08 #5

MindBender77
100+
P: 234
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.

Expand|Select|Wrap|Line Numbers
  1. if Listbox1.selected = "Report1" then
  2. Button1.visible=true
  3. button2.visible = false
  4. etc. etc.
  5. end if
  6.  
Jan 24 '08 #6

100+
P: 132
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.

Expand|Select|Wrap|Line Numbers
  1. if Listbox1.selected = "Report1" then
  2. Button1.visible=true
  3. button2.visible = false
  4. etc. etc.
  5. end if
  6.  
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"
Jan 24 '08 #7

jaxjagfan
Expert 100+
P: 254
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.
Attached Images
File Type: jpg e2e.jpg (20.2 KB, 265 views)
Jan 25 '08 #8

100+
P: 132
[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.
Jan 30 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.