Chart Using A Perameter 2

[deejay33]

Hello All,

This seems like something that someone would have asked but I can't find the answer.

I have a chart that is being built off of a query. The query has the user enter the country they want the report on. The problem is the does not allow a perameter to be entered. It says it [Enter Country:] an invalid field.

I found a TID on Microsfts KB stating you need to use a static criteria. I tested it and it works if I for instance enter France in the criteria under Country. The problem is I want it to ask the user for the country.

There has to be a way to do it. A Macro or Form maybe?

I have not done may reports or forms although I have done quite abit in querying and Tables. I would appreciate the help.

Oh yeah, this is Access 2000.

Daniel Joaquin

 

[BoxHead]

Daniel,

I don't use the charts very often and I've noticed that Chart questions tend to go unanswered, so I'll take a stab at this.

Two ideas:

1.) Create a table tblChartCountry that would hold your static value (field name = 'CountryName') and use an input box or form to update its value before opening the report.
In your query, change the criteria to be DLookUp("[CountryName]","tblChartCountry")

I've used this for static criteria value with Crosstab Queries and it's worked fine.

or

2.) Group your report by Country so that your report contains a page and achart for each country and on the Report's Filter property use [Country] = [Enter Country Name].
In your query, remove the criteria from the country field.

This will limit the report displayed to the country name entered.

HTH


John

 

[dhookom]

First, you should not use parameter prompt queries. Open any canned application and you will find that filtering criteria are always entered into controls on forms. It's not that difficult to kick your development up a notch and use controls.

Second, a chart Row Source is often a crosstab query. You almost always have to declare the data types of your parameters. While in the query design, select Query->Parameters and enter your parameter and data type.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[deejay33]

Thanks for your sugestions. I will try out using a table and form. They will use the form to select a country and then I will put a button that launches the query which will look at the value in the table as imputed by the form.

I will let you know how that goes.

Thanks,
Daniel Joaquin

 

[deejay33]

Thankyou for your help!

I used option 1 discribed by Boxhead. I made a small table called Reports and then in the form I setup the textbox as a dropdown of our countries table. It fills in the Reports Coutry field which is then used in the report. Then I have a button on the form to launch the report.

It works great.

Daniel Joaquin

 

[BoxHead]

Glad to help, Daniel. Thanks for the update.

John