User Supplied Value at Report Run

[Kimbertha]

Hello All:

I am having a brain dead moment. I've done this before but it's been a few years . . .

Can someone remind me how to include a user-supplied value at runtime on a report? I need the report to ask the user the "As of" date for the report, and display that user-supplied information on the report. For my purposes, the "As of" date is not the same as the date the report was run.



For example, on October 6, I am running a report for the period ended September 30. I want my report to display a label that says "As of September 30, 2010" or something similar.

Thanks!

Kim

[pbaldy]

To restrict the underlying data to that period, or just for display on the report? Actually the answer is probably the same. The simplest is probably the bracket method, enclosing a prompt in brackets:

[Enter period end date]

You can use that both in the query or on the report. The more common and more professional approach is to gather user input on a form, then in the query or on the report you can refer to that form:

Forms!FormName.ControlName

[Kimbertha]

Hey pbaldy,

Thanks for your reply!

I just want to display the user-supplied value on the report.

I am familiar with the [Enter whatever] feature, and I remember how to use this in a query, but cannot remember where to insert in the report so that it will display on the report. I tried creating a label and sticking the prompt in there, but Access didn't like that one bit.

Regards,

Kim

[pbaldy]

A textbox with a control source of simply:

=[Enter whatever]

or things like

="As of " & [Enter whatever]

If you're also using it as a query parameter, make sure to spell it exactly the same here, or you'll get prompted twice.

[Kimbertha]

Yeah that's what I thought too. But I get an error message that says:

The Microsoft Access database engine does not recognize '[Enter whatever]' as a valid field name or expression.

Attached is a snip of the textbox and control source. I know this should be simple, and I've done it before, but I just cannot get it right!

[Kimbertha]

Update!

Yes, that syntax does in fact work. But not when the report is based on a crosstab query, which is what my report uses. Oh noooooo!

If I had just a single crosstab query and a single report, I would make a query of the crosstab query and use that for the report. But I have 12 crosstab queries and 12 reports. So I would rather not do that workaround.

I unsuccessfully tried to embed this prompt in the crosstab query. Maybe I need to revisit that.

[pbaldy]

Here's that issue:

http://support.microsoft.com/kb/209778

[Kimbertha]

Hello pbaldy:

That Microsoft KB solves part of the problem. That is good news!

Now the bad news. The report prompts me 3-4 times (depending on how I structure the parameter in the query) to enter the "as of" date in my report whenever I want to open, edit, print-preview, etc. I will not be the end user for this data base, and I know this will be unacceptable to my end-user.

Ideas?

As a remnider, for my purposes the "as of" data is a label only. It will display the quarter end date for the data contained in the report. I am using the same report with refreshed data every quarter, hence my desire to plug in a new quarter end date each quarter when the report is printed.

Kim

[pbaldy]

As I mentioned earlier, the spelling of the bracketed text must match exactly. If you have [Enter as of date] in the query and then =[Enter as of date:] in the report, you'll get prompted twice.

If you're saying that you reopen the report and get prompted again, that is unavoidable with the brackets method. That's one reason most of us use forms to gather user input. As long as the form remains open, the user will not get prompted for input each time.