asked on

Passing Parameters to Report

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?

Thanks.

mbizup

Try this:

DoCmd.OpenReport "rLODDths", acViewPreview, ,Args , acWindowNormal

mbizup

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

Evan Cutler

ASKER

Thanks, Nope...still asking for it. What else you got?

mbizup

What is your actual query (post the SQL for the report's query)?

Evan Cutler

ASKER

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.

Evan Cutler

ASKER

The other values are for other parts of the form.

mbizup

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.

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Evan Cutler

ASKER

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.

mbizup

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).

Evan Cutler

ASKER

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.

Evan Cutler

ASKER

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