Passing param. to Report

kaledev

Registered User.
Local time
Yesterday, 19:03
Joined
Jan 20, 2011
Messages
19
I have a report I am opening in VBA, and the report happens to be based off of a query that has two parameters (Start Date, and End Date). I am attempting to open the report with two variables I am sending via VBA.

So far I have attempted...

Code:
strSDate = "Start Date = #" & txtReportEndDate.TEXT & "#"
DoCmd.OpenReport "SUMMARY", acViewPreview, strSDate

I know this is only attempting to send one parameter, but I have no idea how to send two. But the report seems to ignore what's being sent anyway, and still prompts me for the start date.

Any help would be appreciated. Thanks!
 
Can you not change the query criteria to reference the controls on the form instead of being input box parameters, or is the report/query also used without that form?
 
Yes, there is a potential that the report and query will both be used seperately from the form that I will be running the report from.
 
Are you attempting to hardcode the dates in VBA, if not where are they coming from?
 
Are you attempting to hardcode the dates in VBA, if not where are they coming from?

They are coming from a textbox that is user defined:

Code:
strSDate = txtReportStartDate.Value

The date is spitting out correctly to a msgbox
 
By the looks of the code box in the OP he is trying to define the parameters from two textboxes.

Which makes sense, open it manually and get input boxes, open it via a form used by end-users and it's populated by text boxes on the form.

I was thinking about using VBA in the OnLoad/OnOpen of the report to define the RecordSet based on whether the form is open & controls populated, but I haven't had a chance to look into it.
 
To run anything that that requires parameters by definition the user has to enter them, what way can possibly be easier than the user entering them on a form, that is the way i would always go, or should say always went. :D

Brian
 
There is a space in the field name (don't use them NOR special characters and it makes life much easier) AND you have the where clause in the wrong place:

So this:
strSDate = "Start Date = #" & txtReportEndDate.TEXT & "#"
DoCmd.OpenReport "SUMMARY", acViewPreview, strSDate

Needs to be this
strSDate = "[Start Date]= #" & txtReportEndDate.TEXT & "#"
DoCmd.OpenReport "SUMMARY", acViewPreview, , strSDate
 
Can you not change the query criteria to reference the controls on the form instead of being input box parameters, or is the report/query also used without that form?

I would suggest that is not the best option. It is actually better to open the report by passing the where clause because then you can have a GENERIC query with a GENERIC report and then you can call it any number of ways. If you put the criteria in the query then if you want to use different criteria for a similar but not quite the same report you would then have to build a new query and report.

Try to think REUSABLE whenever building something in Access and it can make things so much nicer in the long run.
 
There is a space in the field name (don't use them NOR special characters and it makes life much easier) AND you have the where clause in the wrong place:

So this:
strSDate = "Start Date = #" & txtReportEndDate.TEXT & "#"
DoCmd.OpenReport "SUMMARY", acViewPreview, strSDate

Needs to be this
strSDate = "[Start Date]= #" & txtReportEndDate.TEXT & "#"
DoCmd.OpenReport "SUMMARY", acViewPreview, , strSDate

I made the corrections and unfortunately I am still getting prompted for the Start Date when the report starts.
 
There is a tutorial here re: Dynamic Reports that might be of interest.
http://www.fontstuff.com/access/acctut19.htm

This is basically a form to gather the parameters before Opening the actual report. Much like Brian has suggested. Just my 2 cents.
 
I made the corrections and unfortunately I am still getting prompted for the Start Date when the report starts.

Are you sure that field has a space in it? Normally when it does that it is trying to find a non-existent object (in this case probably the field). So if the field name is StartDate then using Start Date (with the space) would cause a problem. The same goes for an underscore. If the field is really Start_Date and you try using Start Date then it will squawk.
 
Are you sure that field has a space in it? Normally when it does that it is trying to find a non-existent object (in this case probably the field). So if the field name is StartDate then using Start Date (with the space) would cause a problem. The same goes for an underscore. If the field is really Start_Date and you try using Start Date then it will squawk.

It does have a space. I am not sure if it makes a difference in the syntax but like I said the parameter is inside of the query that the report is pulling from - so it isn't inside the report itself.
 
It does have a space. I am not sure if it makes a difference in the syntax but like I said the parameter is inside of the query that the report is pulling from - so it isn't inside the report itself.
Pull the parameter off of the query and save it. Then use the code I provided. Do NOT try to use both.
 
Pull the parameter off of the query and save it. Then use the code I provided. Do NOT try to use both.

You'll have to excuse my ignorance. How would I pull it out of the query since the query is the one that needs it? Would I somehow tell the query to refer to what the report is recieving instead?

Wouldn't this take away the ability to run the query and report seperately than from only the form? Thanks!
 
You'll have to excuse my ignorance. How would I pull it out of the query since the query is the one that needs it? Would I somehow tell the query to refer to what the report is recieving instead?

The WHERE clause in the DoCmd.OpenReport is passing that information to the QUERY for you.

Wouldn't this take away the ability to run the query and report seperately than from only the form? Thanks!

Okay, yes but it will also give you a generic query and generic report that you can call with DIFFERENT criteria on any of the fields using the same method. You don't have to create a bunch of different queries and reports to be able to accomplish so much more.
 
The WHERE clause in the DoCmd.OpenReport is passing that information to the QUERY for you.



Okay, yes but it will also give you a generic query and generic report that you can call with DIFFERENT criteria on any of the fields using the same method. You don't have to create a bunch of different queries and reports to be able to accomplish so much more.

I am a bit confused on how I would structure this query for that. Does the WHERE condition function like a WHERE clause? My dates are inside of a SELECT as you can see below:

Code:
SELECT [Q03 Units Calculations].*, [T ALLOTMENT].Allowed AS [Allowed Onhand Units], [DAILY CHARGE]*[net onhand units] AS [Daily Charges], IIf([Net Onhand Units]-[Allowed Onhand Units]>0,[Net Onhand Units]-[Allowed Onhand Units],0) AS [Overallowance Units], [OVERAGE CHARGE]*[Overallowance Units] AS [Overallowance Charges], [Daily Charges]+[Overallowance Charges] AS [Total Charges], CDate([End Date]) AS [Max Report Dt], CDate([Start Date]) AS [Min Report Dt]
FROM [T CHARGES], [Q03 Units Calculations] INNER JOIN [T ALLOTMENT] ON [Q03 Units Calculations].Terminal=[T ALLOTMENT].Terminal IN 'test.mdb';

I would love to have only one of these, but I didn't think it was possible as I was told that it is impossible to have a param. inside of an IN clause...and I am pulling from 9 different databases...so 9 queries.

But either way, can you instruct me how one would edit this to allow the report to give the dates? Is it possible since they aren't in a WHERE clause? Thanks for all the help, im trying to learn all of this.
 
I sort of fixed my problem by putting [Forms]![Main]![txtReportEndDate] in place of my start and end dates....so the query pulls the dates from my form, which is fine because it will always be open when ran. If I can figure out how to do the the same with my IN clause I can replace about 20 queries with 3 :).
 
I sort of fixed my problem by putting [Forms]![Main]![txtReportEndDate] in place of my start and end dates....so the query pulls the dates from my form, which is fine because it will always be open when ran.QUOTE]

Wasn't that approach suggested some 5 hours ago?

Brian
 

Users who are viewing this thread

Back
Top Bottom