?
Solved

Passing Parameters to Report

Posted on 2012-08-14
12
Medium Priority
?
535 Views
Last Modified: 2012-08-15
Greetings EE Warriors...
I have this command, that uses a Args string to populate the arguements side of the DoCMD.OpenReport command.

The fields are identical to the report, but the report keeps asking for the fields, as if it doesn't recognize the arguement values.  I know they work because when I do a MSGBox of Args it shows:  "[StartDate] = #2/1/2011# and [EndDate] = #2/28/2011# and [CompareStartDate] = #2/1/2010# and [CompareEndDate] = #2/28/2010#"  Which is correct, and the fields are identical in spelling and case to the reports expected fields.

    Args = "[StartDate] = #" & StartDateEntry & "# and [EndDate] = #" & EndDateEntry & "# and [CompareStartDate] = #" & CmprStartDate & "# and [CompareEndDate] = #" & CmprEndDate & "#"
       
        DoCmd.OpenReport "rLODDths", acViewPreview, , , acWindowNormal, Args

Help?  The report does not respect the arguments.  What do I do?

Start Date
Thanks.
0
Comment
Question by:Evan Cutler
  • 6
  • 6
12 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38293344
Try this:

  DoCmd.OpenReport "rLODDths", acViewPreview, ,Args , acWindowNormal
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38293367
Your order of arguments is important if you are only using commas as in the syntax you posted..

You can also use named arguments (where the order doesn't matter):

 
 DoCmd.OpenReport "rLODDths", View:= acViewPreview, WindowMode:=acWindowNormal, WhereCondition:=Args

Open in new window

0
 
LVL 9

Author Comment

by:Evan Cutler
ID: 38293368
Thanks, Nope...still asking for it. What else you got?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 61

Expert Comment

by:mbizup
ID: 38293386
What is your actual query (post the SQL for the report's query)?
0
 
LVL 9

Author Comment

by:Evan Cutler
ID: 38293398
PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT Count(tOfficer.OfficerID) AS total
FROM tOfficer
WHERE (((tOfficer.DOD)>=[StartDate] And (tOfficer.DOD)<=[EndDate]));

However, the Report is directly tied to the query name, not the SQL itself.
0
 
LVL 9

Author Comment

by:Evan Cutler
ID: 38293399
The other values are for other parts of the form.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38293428
Okay...

Remove the entire WHERE clause.

Your Criteria are provided by the Open Report statement.

Then change Args as follows:

Args = "[DoD]  BETWEEN  #" & StartDateEntry & "# AND #" & EndDateEntry & "# AND [CompareStartDate] = #" & CmprStartDate & "# and [CompareEndDate] = #" & CmprEndDate & "#"

And try the Open Report code I posted again.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 38293438
Sorry - change your query to this:

SELECT Count(tOfficer.OfficerID) AS total
FROM tOfficer

Open in new window


And your code to this:

Args = "[DoD]  BETWEEN  #" & StartDateEntry & "# AND #" & EndDateEntry & "# AND [CompareStartDate] = #" & CmprStartDate & "# and [CompareEndDate] = #" & CmprEndDate & "#"
 DoCmd.OpenReport "rLODDths", View:= acViewPreview, WindowMode:=acWindowNormal, WhereCondition:=Args

Open in new window

0
 
LVL 9

Author Comment

by:Evan Cutler
ID: 38293515
ok...just for clarity.  the "DOD between" goes to the query, then the rest goes to the report?
I have other parts of the form that need these values as well.

Thanks for looking into this...it's helping alot.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38293561
Is the query the recordsource of your report?

The criteria supplied in the WhereCondition of your OpenReport statement determines what is seen in the report (by filltering it's recordsource query or table).
0
 
LVL 9

Author Comment

by:Evan Cutler
ID: 38293587
ok...I guess that's where the long pole of the tent is.

I have a query the report answers to.  So I can see yoru solution.
but the startdate and enddate values also go into textblocks.

Can I not do both at the same time?

Thanks.
0
 
LVL 9

Author Closing Comment

by:Evan Cutler
ID: 38296817
I decided to change it up a bit.
I still have four fields
StartDate, EndDate, CompareStartDate, and CompareEndDate.
What I did was put them all into the master query, then used a Macro to from form to report.

I then put the CompareStartDate and CompareEndDate into invisible textboxes, and had the subreports grab them from there.

thanks for all the help.  Your solution helped me get there.
Evan
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question