Semi-Automatic Parameter Query (2000 SR1)

Topic

New Reply

I have a report based on a parameterized query, with the following a criteria on a date field:
“Between [Enter Begin date?] And [Enter End date?]“. No problem.

I am scheduling the report to be run automatically at a certain time a day, so I changed the parameter to
“Between datBDate() And Date()”
where datBDate is a function that calculates a specific Begin Date. Again no problem

But sometimes, the users still want to run the report interactively, and be able to enter any date range. How can I change the query, so it accepts both forms?
I tried to change the criteria to
IIf(bolAutoRun()=True,(([tblReceipt].[RecvDate]) Between datBDate() And Date()),(([tblReceipt].[RecvDate]) Between [Enter: From what date?] And [Enter: To what date?])).

The bolAutoRun() function returns “True” if the report is run automatically, and “False” if the report is run interactively, but it doesn’t work at all.

I also tried to use a function that returns a string based on bolAutoRun, either “Between [Enter: From what date?] And [Enter: To what date?]” or “Between datBDate() And Date()”, and just place that function into the criteria field, but it didn’t work (I didn’t expect it to, either, makes no sense, since it returns a string, not a date).

I saw a partial solution on The Access Web (http://home.att.net/~dashish/), to pass a parameter to a query via code (partial code snippet included):

Dim qdfParmQry As QueryDef ‘the actual query object
Set qdfParmQry = db.QueryDefs(“Qry1”)
qdfParmQry(“Please Enter Code:”) = 3

‘ or try this alternate method to pass the parameter

qdfParmQry![Please Enter City:] = “New York”
Set rs = qdfParmQry.OpenRecordset()

But I don’t know how to use this with my “Between” parameter, and I don’t know how to use this with a report, which is based on my query.

Any ideas? I’m all fresh out and sure could use some help.

Reply | Quote

Replies

You aren’t going to be able to do it both ways from the query itself. You would be best advised to go with your original criteria (“[Enter Begin date?]”, etc.) and then use code to pass the result of your function to the parameter when you need to run the query from code. The tricky part is that if you use the parameter string in your code to reference the parameter, you have to use *exactly* the same string as you do in the parameter in your query. Even a space that’s different will cause it to fail.

Reply | Quote

Charlotte,
thanks for replying.

When I run the report programmatically, I intend to output the report to a snapshot file using the following code (unless there is a better way):

DoCmd.OutputTo acOutputReport, “rptMyReport”, acFormatSNP, “MyReport.snp”, False

The report’s RecordSource is query “qryMyQuery“, and the query has the “Between [Enter Begin date:] And [Enter End date:]” parameter on a date field.

Could you please give me an example on how to rewrite the above DoCmd, so it feeds the two dates to the “between” parameters, assuming I use my datBDate() function and the Date() function for the two dates?

I really appreciate your help in this.

Klaus

Reply | Quote

OutputTo doesn’t accept parameters, so you’re doing something entirely different from what the code in your previous post addressed. You can’t pass it a parameter when you use OutputTo, so you have to do it one of two ways: Either use a report with a recordsource that references a form’s controls and get the user input from the form, or put the parameters in both the criteria of the query and also in its parameters, which should cause the parameter dialogs to pop up.

Reply | Quote

… why not try using a strInput box or other input prior to the code. The user would then enter a parameter and based on the results, run the query one way or the other? This is not automated but may solve the problem.

Reply | Quote

Unfortunately, that won’t help with an OutputTo command.

Reply | Quote

Thanks all for helping.

I think I will go a route I initially rejected, which is a form with two date fields. The dates will still be populated automatically, but can be changed for manual operation.

I will modify the query to get the dates from the form. This should work in both manual operation, as well as in automated mode when I launch the app in the wee hours .

Let’s hope the customer goes for it .

Klaus

Reply | Quote