|
|
|
User select a date to run a report |
|---|
| message from =?Utf-8?B?c2pvaG5zb24=?= on 21 May 2004 |
I'm trying to run monthly reports that allow a user to select a month and find all the PO's invoiced in that month...
For instance, In my table with PO's, I ahve a date_invoiced field -- if this field is blank, I assume we have not billed the account -- if there is a date, It obviously has been billed.
I want to run a query/report when the user clicks the report icon on the switchboard for this report it asks for what beginning date? (user would enter 5-1-04) and end date? (User would enter 5-31-04) and it would pull all the po's that are invoiced from 5/1 - 5/30 of this year.
Sounds simple but its really killing me.. I cant figure out how to have users have input in the query, and use that to run a report...
Any help would be GREATLY appreciated!
Thank you
|
| Gene replied to =?Utf-8?B?c2pvaG5zb24=?= on 21 May 2004 |
Cut and paste the following and look at it. This works
fine for me using a report request form and then accessing
the parameters on the request form in the "Open" event on
the report. I know this example does not have dates, but
they should work the same way. All you do is apply a
filter to the report itself where the filter is basically
a "WHERE" clause. I intend to have a date range for an
invoice report I am doing soon and I expect this method to
work just fine.
good luck.
_____________________________________________
Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_OpenReport
Me.Filter = ""
If Forms![CostReportRequest]![SelectionCriteria] = 1 Then
DoCmd.ApplyFilter , "User_Id = Forms!
[CostReportRequest]![UserCombo]"
ElseIf Forms![CostReportRequest]![SelectionCriteria] = 2
Then
DoCmd.ApplyFilter , "Vendor_Id = Forms!
[CostReportRequest]![VendorCombo]"
ElseIf Forms![CostReportRequest]![SelectionCriteria] = 3
Then
DoCmd.ApplyFilter , "Vendor_Id = Forms!
[CostReportRequest]![VendorCombo] AND Service_Id = Forms!
[CostReportRequest]![VendServCombo]"
ElseIf Forms![CostReportRequest]![SelectionCriteria] = 4
Then
DoCmd.ApplyFilter , "Department_Id = Forms!
[CostReportRequest]![DeptCombo]"
ElseIf Forms![CostReportRequest]![SelectionCriteria] = 5
Then
DoCmd.ApplyFilter , "Cost_Center_Id = Forms!
[CostReportRequest]![CostCntrCombo]"
ElseIf Forms![CostReportRequest]![SelectionCriteria] = 6
Then
Me.Filter = ""
ElseIf Forms![CostReportRequest]![SelectionCriteria] = 7
Then
DoCmd.ApplyFilter , "Vendor_Id = Forms!
[CostReportRequest]![VendorCombo] AND ServiceType = Forms!
[CostReportRequest]![SrvTypCombo]"
ElseIf Forms![CostReportRequest]![SelectionCriteria] = 8
Then
DoCmd.ApplyFilter , "Parent_Name = Forms!
[CostReportRequest]![ParentCombo]"
ElseIf Forms![CostReportRequest]![SelectionCriteria] = 9
Then
DoCmd.ApplyFilter , "Vendor_Id = Forms!
[CostReportRequest]![VendorCombo] AND Parent_Name = Forms!
[CostReportRequest]![ParentCombo]"
ElseIf Forms![CostReportRequest]![SelectionCriteria] = 10
Then
DoCmd.ApplyFilter , "Vendor_Id = Forms!
[CostReportRequest]![VendorCombo] AND Parent_Name = Forms!
[CostReportRequest]![ParentCombo] AND ServiceType = Forms!
[CostReportRequest]![SrvTypCombo]"
ElseIf Forms![CostReportRequest]![SelectionCriteria] = 11
Then
DoCmd.ApplyFilter , "Vendor_Id = Forms!
[CostReportRequest]![VendorCombo] AND Cost_Center_Id =
Forms![CostReportRequest]![CostCntrCombo]AND
InvoiceGroupNbr = Forms![CostReportRequest]![InvGrpCombo]"
ElseIf Forms![CostReportRequest]![SelectionCriteria] = 12
Then
DoCmd.ApplyFilter , "Vendor_Id = Forms!
[CostReportRequest]![VendorCombo] AND InvoiceGroupNbr =
Forms![CostReportRequest]![InvGrpCombo]"
End If
Err_OpenReport:
Me.Filter = ""
Exit Sub
cant find the correct answer...
select a month and find all the PO's invoiced in that
month...
date_invoiced field -- if this field is blank, I assume we
have not billed the account -- if there is a date, It
obviously has been billed.
report icon on the switchboard for this report it asks for
what beginning date? (user would enter 5-1-04) and end
date? (User would enter 5-31-04) and it would pull all the
po's that are invoiced from 5/1 - 5/30 of this year.
out how to have users have input in the query, and use
that to run a report...
|
| =?Utf-8?B?c2pvaG5zb24=?= replied to Gene on 21 May 2004 |
Thanks!
|
| anonymous replied to =?Utf-8?B?U2pvaG5zb24=?= on 21 May 2004 |
Just collect the two dates on the "request" form, then do
the filter logic in the "open" event of the report. In
the filter logic use the "BETWEEN" operator as if you were
invoicedate >= forms![xxx]![fromDate] AND invoicedate <=
forms![xxx]![toDate]
as his example shows, make sure report properties allow
filtering...
Gene
us/default.mspx?dg=microsoft.public.access&tid=2fda7f06-
f8c2-462e-9a01-d6b8711eb9e7&p=2&shell=/office/community/en-
us/Configuration.xml
this in a little simpler terms?
|
| John Vinson replied to =?Utf-8?B?c2pvaG5zb24=?= on 21 May 2004 |
A "Parameter Query" is the way to go here. The simplest way would be
to have a criterion on Date_Invoiced of
BETWEEN [Enter start date:] AND [Enter end date:]
A bit more flexibly, you could have a Form on which the user could
enter the dates; if the form is named frmCrit with textboxes txtStart
and txtEnd, you could use criteria
BETWEEN [Forms]![frmCrit]![txtStart] AND [Forms]![frmCrit]![txtEnd]
Even better for the user - have two listboxes on the form with years
and months; in the month listbox be sure the bound column is the month
number, 1 to 12, perhaps with the month name. You can set the default
property of the year listbox to Year(Date()) so the current year is
automatically selected. You would then use a criterion of
< DateSerial(Forms!frmCrit!lstYear, Forms!frmCrit!lstMonth+1, 1)
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
|
|
