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?

Start Date
Thanks.
LVL 9
Evan CutlerVolunteer Chief Information OfficerAsked:
Who is Participating?
 
mbizupCommented:
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
 
mbizupCommented:
Try this:

  DoCmd.OpenReport "rLODDths", acViewPreview, ,Args , acWindowNormal
0
 
mbizupCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
Thanks, Nope...still asking for it. What else you got?
0
 
mbizupCommented:
What is your actual query (post the SQL for the report's query)?
0
 
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
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
 
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
The other values are for other parts of the form.
0
 
mbizupCommented:
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
 
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
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
 
mbizupCommented:
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
 
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
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
 
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.