JosephEricDavis
asked on
Access 2007 VBA - Open report with filtering
This one should be quick and easy...
I've got a tabular report bound to a query. I need to open that report from a form where I select a projectID and a date range. In a button click event on the form I am able to open it using this...
DoCmd.OpenReport "rptExpenditureReport", acViewReport
But how do I pass parameters to the report and how do I utilize those parameters as filters?
Thanks
I've got a tabular report bound to a query. I need to open that report from a form where I select a projectID and a date range. In a button click event on the form I am able to open it using this...
DoCmd.OpenReport "rptExpenditureReport", acViewReport
But how do I pass parameters to the report and how do I utilize those parameters as filters?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Feeding values from controls directly to a report can be problematic, because of data conversion and/or Nulls or empty strings in the controls.
ASKER
I'm not quite sure exactly how the date range condition is being put together. Can you give me an example of how the final conditional string is supposed to turn out if the date field I'm filtering on is called ExpenditureDate?
Thanks
Thanks
If the criteria is numeric then you need nothing around the criteria like:
"[ProjectID]=" & Me.ProjectID
If the criteria is a string then you need ' 's around the criteria like:
"[ProjectID]='" & Me.ProjectID & "'"
If the criteria is a date then you need #'s around the criteria like:
"[ExpenditureDate]=#" & Me.ExpenditureDate & "#"
So the final result would be (assuming ProjectID is numeric)
"[ProjectID]=" & Me.ProjectID & " AND [ExpenditureDate]=#" & Me.ExpenditureDate & "#"
Your question seems to ask about a date range which I initially missed. So you have two dates selected on your form, and you want to return values where [ExpenditureDate] is between those two values?
"[ProjectID]=" & Me.ProjectID
If the criteria is a string then you need ' 's around the criteria like:
"[ProjectID]='" & Me.ProjectID & "'"
If the criteria is a date then you need #'s around the criteria like:
"[ExpenditureDate]=#" & Me.ExpenditureDate & "#"
So the final result would be (assuming ProjectID is numeric)
"[ProjectID]=" & Me.ProjectID & " AND [ExpenditureDate]=#" & Me.ExpenditureDate & "#"
Your question seems to ask about a date range which I initially missed. So you have two dates selected on your form, and you want to return values where [ExpenditureDate] is between those two values?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window