Parameter Passing to a Report

[lawsonbooth]

Let me state right from the beginning that I am in over my head when it comes to queries calling queries. Any help would be greatly appreciated. I have read so much my eyes hurt and now I am totally lost. If the answer I need is in the forum please point me to it.
Here is the situation: (I did not create this report.) I am running a report using the “DoCmd.OutputTo” in a VBA module. The report’s data source is a query which is the result of another query with the input date range prompts on the other query. The report is always run for the previous work day. In my VBA module I want to figure the previous day’s date and pass it to the report process so that I can automate the report running without having to enter a date range. How do you pass data to a query prompt in a VBA nodule? Is this even possible?
LawsonBooth:banghead:

[OBP]

If the query is only used for this report then you don't need to pass it using VBA, all you need to do is place

Date() - 1

in the first Criteria Row of the required date field in the query.

[lawsonbooth]

OBP, thank you for your response. The report is used by others who need the date range input. I was hoping that I would not have to create a copy of the quieries just for my process. Thanks again for your excellant suggestion.

Lawson

[OBP]

Is the Report opened from a Form?

[lawsonbooth]

No, there are no forms involved in any of the queries.

My VBA module has the following line:

DoCmd.OutputTo acOutputReport, "rptSummaryByDate", acFormatRTF, xPath & "OutboundCallSummary.rtf"

The report "rptSummaryByDate" data source is a query which is the result of another query and that other query has the date prompts.

[OBP]

Well why not reproduce the queries and report to use your date instead, because if the Parameters are in the first query you can't change them with VBA.

Alternatively you can remove the Parameters and then use a QueryDef to rewrite the Query with VBA generated SQL which would include the use of 2 input message boxes to get the 2 dates that others would need or using the Date -1 that you need. But it is a lot of work compared to just copying the queries and report.

[lawsonbooth]

OBP, I have decided to just copy the queries and modify for my process. I really appreciate your time. The help that this forum provides is just outstanding!

Lawson