User select a date to run a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all, I have been looking through the help files, and I cant find the correct answer..

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 yo
 
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
-----Original Message-----
Hi all, I have been looking through the help files, and I
Click to expand...
cant find the correct answer...
I'm trying to run monthly reports that allow a user to
Click to expand...
select a month and find all the PO's invoiced in that
month...
For instance, In my table with PO's, I ahve a
Click to expand...
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
Click to expand...
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
Click to expand...
out how to have users have input in the query, and use
that to run a report...
 
Thanks for the answer Gene - unfortunately, I dont understand it.. I'm going to have to go much more basic, I guess...

Thanks!
 
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
writing a "WHERE" clause or set report filter with
something like:

invoicedate >= forms![xxx]![fromDate] AND invoicedate <=
forms![xxx]![toDate]

as his example shows, make sure report properties allow
filtering...

Gene
-----Original Message-----
I did a search here and found this link

http://www.microsoft.com/office/community/en- us/default.mspx?dg=microsoft.public.access&tid=2fda7f06-
f8c2-462e-9a01-d6b8711eb9e7&p=2&shell=/office/community/en-
us/Configuration.xml

however, that really confuses me, could someone explain
Click to expand...
this in a little simpler terms?
 
Hi all, I have been looking through the help files, and I cant find the correct answer...

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
Click to expand...

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) AND
Click to expand...
< DateSerial(Forms!frmCrit!lstYear, Forms!frmCrit!lstMonth+1, 1)
 
Back
Top